<?xml version="1.0" encoding="UTF-8" ?>

<DataIntegratorExport repositoryVersion="12.2.2.0000" productVersion="12.2.3.2">

	<DIDatabaseDatastore name="DW_METADATA_DS" typeId="3" >

		<DIAttributes>

			<DIAttribute name="DBLiveLoad" value="no"/>

			<DIAttribute name="application_type" value="Custom"/>

			<DIAttribute name="cdc_enabled" value="no"/>

			<DIAttribute name="datastore_repotype" value="local"/>

			<DIAttribute name="ds_configuration_enabled" value="yes"/>

			<DIAttribute name="ds_configurations" hasNestedXMLTree ="true">


				<DSConfigurations>
					<DSConfiguration default="true" name="Default">
						<case_sensitive>no</case_sensitive>
						<database_type>Microsoft_SQL_Server</database_type>
						<db_alias_name1>DW_METADATA_ALIAS</db_alias_name1>
						<db_alias_user1>dwmetadata</db_alias_user1>
						<loader_xact_size>1000</loader_xact_size>
						<locale_codepage>&lt;default&gt;</locale_codepage>
						<locale_language>&lt;default&gt;</locale_language>
						<locale_territory>&lt;default&gt;</locale_territory>
						<mssql_windows_authentication>no</mssql_windows_authentication>
						<password>;085A6DF93DA01ED1DEA3BB1DC1D656C0EF56E65913FB53605B75804428674DE2</password>
						<sql_server_database>XsStaging</sql_server_database>
						<sql_server_dataserver>localhost,1433</sql_server_dataserver>
						<sql_server_version>Microsoft SQL Server 2005</sql_server_version>
						<user>dwmetadata</user>
					</DSConfiguration>
				</DSConfigurations>


			</DIAttribute>

			<DIAttribute name="enable_data_transfer" value="yes"/>

			<DIAttribute name="import_unknown_type" value="no"/>

			<DIAttribute name="name" value="DW_METADATA_DS"/>

			<DIAttribute name="unknown_type_size" value="255"/>

		</DIAttributes>

	</DIDatabaseDatastore>

	<DIDatabaseDatastore name="SRCSTAGING_DS" typeId="3" >

		<DIAttributes>

			<DIAttribute name="DBLiveLoad" value="no"/>

			<DIAttribute name="application_type" value="Custom"/>

			<DIAttribute name="cdc_enabled" value="no"/>

			<DIAttribute name="datastore_repotype" value="local"/>

			<DIAttribute name="ds_configuration_enabled" value="yes"/>

			<DIAttribute name="ds_configurations" hasNestedXMLTree ="true">


				<DSConfigurations>
					<DSConfiguration default="true" name="Default">
						<case_sensitive>no</case_sensitive>
						<database_type>Microsoft_SQL_Server</database_type>
						<db_alias_name1>SRCSTAGING_ALIAS</db_alias_name1>
						<db_alias_user1>dws</db_alias_user1>
						<loader_xact_size>1000</loader_xact_size>
						<locale_codepage>&lt;default&gt;</locale_codepage>
						<locale_language>&lt;default&gt;</locale_language>
						<locale_territory>&lt;default&gt;</locale_territory>
						<mssql_windows_authentication>no</mssql_windows_authentication>
						<password>;085A6DF93DA01ED1DEA3BB1DC1D656C0EF56E65913FB53605B75804428674DE2</password>
						<sql_server_database>XsStaging</sql_server_database>
						<sql_server_dataserver>localhost,1433</sql_server_dataserver>
						<sql_server_version>Microsoft SQL Server 2005</sql_server_version>
						<user>dws</user>
					</DSConfiguration>
				</DSConfigurations>


			</DIAttribute>

			<DIAttribute name="enable_data_transfer" value="yes"/>

			<DIAttribute name="import_unknown_type" value="no"/>

			<DIAttribute name="name" value="SRCSTAGING_DS"/>

			<DIAttribute name="unknown_type_size" value="255"/>

		</DIAttributes>

	</DIDatabaseDatastore>

	<DIDatabaseDatastore name="DW_DS" typeId="3" >

		<DIAttributes>

			<DIAttribute name="DBLiveLoad" value="no"/>

			<DIAttribute name="application_type" value="Custom"/>

			<DIAttribute name="cdc_enabled" value="no"/>

			<DIAttribute name="datastore_repotype" value="local"/>

			<DIAttribute name="ds_configuration_enabled" value="yes"/>

			<DIAttribute name="ds_configurations" hasNestedXMLTree ="true">


				<DSConfigurations>
					<DSConfiguration default="true" name="Default">
						<case_sensitive>no</case_sensitive>
						<database_type>Microsoft_SQL_Server</database_type>
						<db_alias_name1>DW_ALIAS</db_alias_name1>
						<db_alias_user1>dwt</db_alias_user1>
						<loader_xact_size>1000</loader_xact_size>
						<locale_codepage>&lt;default&gt;</locale_codepage>
						<locale_language>&lt;default&gt;</locale_language>
						<locale_territory>&lt;default&gt;</locale_territory>
						<mssql_windows_authentication>no</mssql_windows_authentication>
						<password>;C7D0EC161CA52183B3F6AA62065D16D75A3E6AAF455B3AE46139988B48682A4A</password>
						<sql_server_database>XsTarget</sql_server_database>
						<sql_server_dataserver>localhost,1433</sql_server_dataserver>
						<sql_server_version>Microsoft SQL Server 2005</sql_server_version>
						<user>dwt</user>
					</DSConfiguration>
				</DSConfigurations>


			</DIAttribute>

			<DIAttribute name="enable_data_transfer" value="yes"/>

			<DIAttribute name="import_unknown_type" value="no"/>

			<DIAttribute name="name" value="DW_DS"/>

			<DIAttribute name="unknown_type_size" value="255"/>

		</DIAttributes>

	</DIDatabaseDatastore>

	<DIDatabaseDatastore name="DWSTAGING_DS" typeId="3" >

		<DIAttributes>

			<DIAttribute name="DBLiveLoad" value="no"/>

			<DIAttribute name="application_type" value="Custom"/>

			<DIAttribute name="cdc_enabled" value="no"/>

			<DIAttribute name="datastore_repotype" value="local"/>

			<DIAttribute name="ds_configuration_enabled" value="yes"/>

			<DIAttribute name="ds_configurations" hasNestedXMLTree ="true">


				<DSConfigurations>
					<DSConfiguration default="true" name="Default">
						<case_sensitive>no</case_sensitive>
						<database_type>Microsoft_SQL_Server</database_type>
						<db_alias_name1>DWSTAGING_ALIAS</db_alias_name1>
						<db_alias_user1>dwst</db_alias_user1>
						<loader_xact_size>1000</loader_xact_size>
						<locale_codepage>&lt;default&gt;</locale_codepage>
						<locale_language>&lt;default&gt;</locale_language>
						<locale_territory>&lt;default&gt;</locale_territory>
						<mssql_windows_authentication>no</mssql_windows_authentication>
						<password>;9FA3E42F1AB29C1A240FBFA241A83F373415575169CA3130A2F2C50213AED23D</password>
						<sql_server_database>XsStaging</sql_server_database>
						<sql_server_dataserver>localhost,1433</sql_server_dataserver>
						<sql_server_version>Microsoft SQL Server 2005</sql_server_version>
						<user>dwst</user>
					</DSConfiguration>
				</DSConfigurations>


			</DIAttribute>

			<DIAttribute name="enable_data_transfer" value="yes"/>

			<DIAttribute name="import_unknown_type" value="no"/>

			<DIAttribute name="name" value="DWSTAGING_DS"/>

			<DIAttribute name="unknown_type_size" value="255"/>

		</DIAttributes>

	</DIDatabaseDatastore>

	<DIDatabaseDatastore name="DW_ABC_DS" typeId="3" >

		<DIAttributes>

			<DIAttribute name="DBLiveLoad" value="no"/>

			<DIAttribute name="application_type" value="Custom"/>

			<DIAttribute name="cdc_enabled" value="no"/>

			<DIAttribute name="datastore_repotype" value="local"/>

			<DIAttribute name="ds_configuration_enabled" value="yes"/>

			<DIAttribute name="ds_configurations" hasNestedXMLTree ="true">


				<DSConfigurations>
					<DSConfiguration default="true" name="Default">
						<case_sensitive>no</case_sensitive>
						<database_type>Microsoft_SQL_Server</database_type>
						<db_alias_name1>DW_ABC_ALIAS</db_alias_name1>
						<db_alias_user1>dwabc</db_alias_user1>
						<loader_xact_size>1000</loader_xact_size>
						<locale_codepage>&lt;default&gt;</locale_codepage>
						<locale_language>&lt;default&gt;</locale_language>
						<locale_territory>&lt;default&gt;</locale_territory>
						<mssql_windows_authentication>no</mssql_windows_authentication>
						<password>;89770408927C5ECAB76D9855E0DA5FE4F25B0E2949263E3397D5BD72B1A30930</password>
						<sql_server_database>XsStaging</sql_server_database>
						<sql_server_dataserver>localhost,1433</sql_server_dataserver>
						<sql_server_version>Microsoft SQL Server 2005</sql_server_version>
						<user>dwabc</user>
					</DSConfiguration>
				</DSConfigurations>


			</DIAttribute>

			<DIAttribute name="enable_data_transfer" value="yes"/>

			<DIAttribute name="import_unknown_type" value="no"/>

			<DIAttribute name="name" value="DW_ABC_DS"/>

			<DIAttribute name="unknown_type_size" value="255"/>

		</DIAttributes>

	</DIDatabaseDatastore>

	<DITable name="ConsolidationDimRef" owner="dwmetadata" datastore="DW_METADATA_DS" database="XsStaging" description="" >

		<DIProperties>

			<DIAttribute name="Table_Type" value="TABLE" />

			<DIAttribute name="Estimated_Row_Count" value="50000" />

			<DIAttribute name="Loader_Is_Template_Table" value="no" />

			<DIAttribute name="db_alias_name" value="DW_METADATA_ALIAS" />

		</DIProperties>

		<DIColumn name="Hjid" datatype="DECIMAL" precision="19" scale="0" nullable="false" >

		</DIColumn>

		<DIColumn name="Hjtype" datatype="VARCHAR" size="255" nullable="false" >

		</DIColumn>

		<DIColumn name="Dimensionidentifier" datatype="VARCHAR" size="255" nullable="true" >

		</DIColumn>

		<DIColumn name="Consolidation_hjid" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="Hjsortorder" datatype="DECIMAL" precision="10" scale="0" nullable="true" >

		</DIColumn>

		<DIPrimaryKey>

			<DIPrimaryKeyColumn name="Hjid" />

		</DIPrimaryKey>

		<DITableIndex name="PK_CONSOLIDATIONDIMREF" unique="true">

			<DIIndexColumn name="Hjid" />

		</DITableIndex>

	</DITable>

	<DITable name="ConsolidationFactRef" owner="dwmetadata" datastore="DW_METADATA_DS" database="XsStaging" description="" >

		<DIProperties>

			<DIAttribute name="Table_Type" value="TABLE" />

			<DIAttribute name="Estimated_Row_Count" value="50000" />

			<DIAttribute name="Loader_Is_Template_Table" value="no" />

			<DIAttribute name="db_alias_name" value="DW_METADATA_ALIAS" />

		</DIProperties>

		<DIColumn name="Hjid" datatype="DECIMAL" precision="19" scale="0" nullable="false" >

		</DIColumn>

		<DIColumn name="Hjtype" datatype="VARCHAR" size="255" nullable="false" >

		</DIColumn>

		<DIColumn name="Factidentifier" datatype="VARCHAR" size="255" nullable="true" >

		</DIColumn>

		<DIColumn name="Consolidation_hjid" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="Hjsortorder" datatype="DECIMAL" precision="10" scale="0" nullable="true" >

		</DIColumn>

		<DIPrimaryKey>

			<DIPrimaryKeyColumn name="Hjid" />

		</DIPrimaryKey>

		<DITableIndex name="PK_CONSOLIDATIONFACTREF" unique="true">

			<DIIndexColumn name="Hjid" />

		</DITableIndex>

	</DITable>

	<DITable name="ConsolidationModel" owner="dwmetadata" datastore="DW_METADATA_DS" database="XsStaging" description="" >

		<DIProperties>

			<DIAttribute name="Table_Type" value="TABLE" />

			<DIAttribute name="Estimated_Row_Count" value="50000" />

			<DIAttribute name="Loader_Is_Template_Table" value="no" />

			<DIAttribute name="db_alias_name" value="DW_METADATA_ALIAS" />

		</DIProperties>

		<DIColumn name="Hjid" datatype="DECIMAL" precision="19" scale="0" nullable="false" >

		</DIColumn>

		<DIColumn name="Hjtype" datatype="VARCHAR" size="255" nullable="false" >

		</DIColumn>

		<DIColumn name="Tabledescription" datatype="VARCHAR" size="255" nullable="true" >

		</DIColumn>

		<DIColumn name="AutoMapping" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="BypassSSIStepPPM" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="BypassSSIStepAM" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="BypassSSIStepSM" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="Businessname" datatype="VARCHAR" size="255" nullable="true" >

		</DIColumn>

		<DIColumn name="Dwid" datatype="VARCHAR" size="80" nullable="true" >

		</DIColumn>

		<DIPrimaryKey>

			<DIPrimaryKeyColumn name="Hjid" />

		</DIPrimaryKey>

		<DITableIndex name="PK_CONSOLIDATIONMODEL" unique="true">

			<DIIndexColumn name="Hjid" />

		</DITableIndex>

	</DITable>

	<DITable name="CONTROL" owner="dws" datastore="SRCSTAGING_DS" database="XsStaging" description="" >

		<DIProperties>

			<DIAttribute name="Table_Type" value="TABLE" />

			<DIAttribute name="Estimated_Row_Count" value="50000" />

			<DIAttribute name="Loader_Is_Template_Table" value="no" />

			<DIAttribute name="db_alias_name" value="SRCSTAGING_ALIAS" />

		</DIProperties>

		<DIColumn name="MD_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="MD_PROCESS_ID" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="RESTREAM_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" nullable="true" >

		</DIColumn>

		<DIColumn name="ENTITY_NAME" datatype="VARCHAR" size="100" nullable="true" >

		</DIColumn>

		<DIColumn name="EXTRACT_ENTITY_NAME" datatype="VARCHAR" size="100" nullable="true" >

		</DIColumn>

		<DIColumn name="EXTRACT_TSNP_FLAG" datatype="VARCHAR" size="1" nullable="true" >

		</DIColumn>

		<DIColumn name="TYPE" datatype="VARCHAR" size="30" nullable="true" >

		</DIColumn>

		<DIColumn name="STATUS" datatype="VARCHAR" size="30" nullable="true" >

		</DIColumn>

		<DIColumn name="CREATEDDATETIME" datatype="DATETIME" nullable="true" >

		</DIColumn>

		<DIColumn name="MODIFIEDDATETIME" datatype="DATETIME" nullable="true" >

		</DIColumn>

	</DITable>

	<DIFunction name="CleanData_trg" owner="dwt" datastore="DW_DS" >

		<DIProperties>

			<DIAttribute name="CallableFromSQLExpr" value="NO"/>

			<DIAttribute name="SavedAfterCheckOut" value="YES"/>

			<DIAttribute name="db_alias_name" value="DW_ALIAS"/>

			<DIAttribute name="Date_created" value="Mon Aug 26 11:24:47 2013"/>

			<DIAttribute name="Date_modified" value="Mon Nov 19 17:33:01 2012"/>

		</DIProperties>

		<DIFunctionParameters>

			<DIElement paramType="IN" name="entityname" datatype="VARCHAR" size="100" >

			</DIElement>

			<DIElement paramType="IN" name="batcheid" datatype="INT" >

			</DIElement>

			<DIElement paramType="OUT" name="AL_SP_RETCODE" datatype="VARCHAR" size="256" >

			</DIElement>

			<DIElement paramType="OUT" name="AL_SP_ERRMSG" datatype="VARCHAR" size="1024" >

			</DIElement>

		</DIFunctionParameters>

		<DIFunctionReturn datatype="INT" />

	</DIFunction>

	<DIFunction name="CleanData_stg" owner="dwst" datastore="DWSTAGING_DS" >

		<DIProperties>

			<DIAttribute name="CallableFromSQLExpr" value="NO"/>

			<DIAttribute name="SavedAfterCheckOut" value="YES"/>

			<DIAttribute name="db_alias_name" value="DWSTAGING_ALIAS"/>

			<DIAttribute name="Date_created" value="Mon Aug 26 11:24:47 2013"/>

			<DIAttribute name="Date_modified" value="Mon Nov 19 17:33:02 2012"/>

		</DIProperties>

		<DIFunctionParameters>

			<DIElement paramType="IN" name="entityname" datatype="VARCHAR" size="100" >

			</DIElement>

			<DIElement paramType="IN" name="batcheid" datatype="INT" >

			</DIElement>

			<DIElement paramType="OUT" name="AL_SP_RETCODE" datatype="VARCHAR" size="256" >

			</DIElement>

			<DIElement paramType="OUT" name="AL_SP_ERRMSG" datatype="VARCHAR" size="1024" >

			</DIElement>

		</DIFunctionParameters>

		<DIFunctionReturn datatype="INT" />

	</DIFunction>

	<DIScriptFunction name="ABC_ENDJB_FN" typeId="11" >

		<DIUIOptions>

			<DIAttribute name="ui_script_text" value="#UPDATE STEP_RUNTIME RECORD WITH SUCCESS STATUS AND END TIME&#xA;#GET JOB_ID&#xA;&#xA;$l_status = 'SUCCESS';&#xA;#$now = sysdate();&#xA;&#xA;$l_wf_failed_count = sql('DW_ABC_DS',' select count(status) from JOB_STREAM_STEP_DETAIL_RT&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND (status = \'ERROR\' or status = \'MAX_EXECUTION_TIME_EXCEEDED\')');&#xA;ETL_PRINT( '**     wf_failed_count = {$l_wf_failed_count}' );&#xA;&#xA;$l_wf_success_count = sql('DW_ABC_DS',' select count(status) from JOB_STREAM_STEP_DETAIL_RT&#xA;                     where MD_PROCESS_ID = {$p_process_id} AND status = \'SUCCESS\'');&#xA;ETL_PRINT( '**     wf_success_count = {$l_wf_success_count}' );&#xA;&#xA;$l_am_failed_count = sql('DW_ABC_DS',' select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND (status = \'ERROR\' or status = \'MAX_EXECUTION_TIME_EXCEEDED\')');&#xA;ETL_PRINT( '**     am_failed_count = {$l_am_failed_count}' );&#xA;&#xA;$l_am_warning_count = sql('DW_ABC_DS',' select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND status = \'WARNING\'');&#xA;ETL_PRINT( '**     am_warning_count = {$l_am_warning_count}' );&#xA;&#xA;$l_am_success_count = sql('DW_ABC_DS',' select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND status = \'SUCCESS\'');&#xA;ETL_PRINT( '**     am_success_count = {$l_am_success_count}' );&#xA;&#xA;$l_status_info = 'WFs statuses: success: ' || $l_wf_success_count || ', error: '  || $l_wf_failed_count ||' || Audit metrics: success: ' || $l_am_success_count || ', warning: ' || $l_am_warning_count || ', error: ' || $l_am_failed_count || '.';&#xA;&#xA;if ($l_wf_failed_count &gt; 0)&#xA;begin&#xA;&#x9;$l_status = 'ERROR';&#xA;&#x9;$l_reco_action =  'Drill to WF level to access the error message. ';&#xA;end&#x9;&#xA;else if ($l_am_failed_count &gt; 0)&#xA;begin&#xA;&#x9;$l_status = 'ERROR'; &#xA;&#x9;$l_reco_action = $l_reco_action || 'Refer to failed metrics. ';&#xA;end&#xA;else if ($l_am_warning_count &gt; 0)&#xA;begin&#xA;&#x9;$l_status = 'WARNING';&#xA;&#x9;$l_reco_action = $l_reco_action || 'Check WARNING metrics. ';&#xA;end&#xA;&#xA;&#xA;#sql('DW_ABC_DS','update JOB_STREAM_STEP_RT&#xA;#               set STATUS = {$l_status}, STATE = \'FINISHED\' , END_TIME = {sysdate()}, STATUS_INFO = {$l_status_info}, RECO_ACTION = {$l_reco_action}&#xA;#               where MD_PROCESS_ID = {$p_process_id}');&#xA;&#xA;sql('DW_ABC_DS','update JOB_STREAM_STEP_RT&#xA;               set STATUS = {$l_status}, STATE = \'FINISHED\' , END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127), STATUS_INFO = {$l_status_info}, RECO_ACTION = {$l_reco_action}&#xA;               where MD_PROCESS_ID = {$p_process_id}');&#xA;ETL_PRINT( '**' );&#xA;ETL_PRINT( '**' );&#xA;ETL_PRINT( '** The Job {job_name()} is ending' );&#xA;ETL_PRINT( '**   status: {$l_status}' );&#xA;ETL_PRINT( '**   explanation: {$l_status_info})' );&#xA;ETL_PRINT( '********************************' );&#xA;ETL_PRINT( '********************************' );&#xA;&#xA;return NULL;&#xA;"/>

		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="Acta_user_func_category" value="User_Script_Function"/>

		</DIProperties>

		<DIParameters>
			<DIElement paramType="IN" name="$p_process_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

		</DIParameters>

		<DIVariables>
			<DIElement paramType="LOCAL" name="$l_wf_failed_count" datatype="INT" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_status" datatype="VARCHAR" size="10" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_wf_success_count" datatype="INT" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_status_info" datatype="VARCHAR" size="400" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_am_failed_count" datatype="INT" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_am_warning_count" datatype="INT" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_am_success_count" datatype="INT" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_reco_action" datatype="VARCHAR" size="4000" >

			</DIElement>

		</DIVariables>

		<DIFunctionReturn datatype="INT" />

		<DISteps>

			<DIAssignmentStep typeId="12" variable="$l_status" >

				<DIExpression isString="true" expr="'SUCCESS'" >

					<CONSTANT value="SUCCESS" datatype="VARCHAR" size="7" />

				</DIExpression>

			</DIAssignmentStep>

			<DIAssignmentStep typeId="12" variable="$l_wf_failed_count" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', ' select count(status) from JOB_STREAM_STEP_DETAIL_RT&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND (status = \'ERROR\' or status = \'MAX_EXECUTION_TIME_EXCEEDED\')')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value=" select count(status) from JOB_STREAM_STEP_DETAIL_RT&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND (status = 'ERROR' or status = 'MAX_EXECUTION_TIME_EXCEEDED')" datatype="VARCHAR" size="175" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**     wf_failed_count = {$l_wf_failed_count}')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**     wf_failed_count = {$l_wf_failed_count}" datatype="VARCHAR" size="45" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIAssignmentStep typeId="12" variable="$l_wf_success_count" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', ' select count(status) from JOB_STREAM_STEP_DETAIL_RT&#xA;                     where MD_PROCESS_ID = {$p_process_id} AND status = \'SUCCESS\'')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value=" select count(status) from JOB_STREAM_STEP_DETAIL_RT&#xA;                     where MD_PROCESS_ID = {$p_process_id} AND status = 'SUCCESS'" datatype="VARCHAR" size="134" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**     wf_success_count = {$l_wf_success_count}')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**     wf_success_count = {$l_wf_success_count}" datatype="VARCHAR" size="47" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIAssignmentStep typeId="12" variable="$l_am_failed_count" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', ' select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND (status = \'ERROR\' or status = \'MAX_EXECUTION_TIME_EXCEEDED\')')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value=" select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND (status = 'ERROR' or status = 'MAX_EXECUTION_TIME_EXCEEDED')" datatype="VARCHAR" size="162" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**     am_failed_count = {$l_am_failed_count}')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**     am_failed_count = {$l_am_failed_count}" datatype="VARCHAR" size="45" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIAssignmentStep typeId="12" variable="$l_am_warning_count" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', ' select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND status = \'WARNING\'')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value=" select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND status = 'WARNING'" datatype="VARCHAR" size="120" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**     am_warning_count = {$l_am_warning_count}')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**     am_warning_count = {$l_am_warning_count}" datatype="VARCHAR" size="47" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIAssignmentStep typeId="12" variable="$l_am_success_count" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', ' select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND status = \'SUCCESS\'')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value=" select count(status) from AUDIT_METRIC&#xA;                    where MD_PROCESS_ID = {$p_process_id} AND status = 'SUCCESS'" datatype="VARCHAR" size="120" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**     am_success_count = {$l_am_success_count}')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**     am_success_count = {$l_am_success_count}" datatype="VARCHAR" size="47" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIAssignmentStep typeId="12" variable="$l_status_info" >

				<DIExpression isString="true" expr="(((((((((('WFs statuses: success: ' || $l_wf_success_count) || ', error: ') || $l_wf_failed_count) || ' || Audit metrics: success: ') || $l_am_success_count) || ', warning: ') || $l_am_warning_count) || ', error: ') || $l_am_failed_count) || '.')" >

					<CONCAT>

						<CONCAT>

							<CONCAT>

								<CONCAT>

									<CONCAT>

										<CONCAT>

											<CONCAT>

												<CONCAT>

													<CONCAT>

														<CONCAT>

															<CONSTANT value="WFs statuses: success: " datatype="VARCHAR" size="23" />

															<VARIABLE_REFERENCE name="$l_wf_success_count" />

														</CONCAT>

														<CONSTANT value=", error: " datatype="VARCHAR" size="9" />

													</CONCAT>

													<VARIABLE_REFERENCE name="$l_wf_failed_count" />

												</CONCAT>

												<CONSTANT value=" || Audit metrics: success: " datatype="VARCHAR" size="28" />

											</CONCAT>

											<VARIABLE_REFERENCE name="$l_am_success_count" />

										</CONCAT>

										<CONSTANT value=", warning: " datatype="VARCHAR" size="11" />

									</CONCAT>

									<VARIABLE_REFERENCE name="$l_am_warning_count" />

								</CONCAT>

								<CONSTANT value=", error: " datatype="VARCHAR" size="9" />

							</CONCAT>

							<VARIABLE_REFERENCE name="$l_am_failed_count" />

						</CONCAT>

						<CONSTANT value="." datatype="VARCHAR" size="1" />

					</CONCAT>

				</DIExpression>

			</DIAssignmentStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="($l_wf_failed_count &gt; 0)" >

					<LOGICAL_OP op="GT">

						<VARIABLE_REFERENCE name="$l_wf_failed_count" />

						<CONSTANT value="0" datatype="INT" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIAssignmentStep typeId="12" variable="$l_status" >

						<DIExpression isString="true" expr="'ERROR'" >

							<CONSTANT value="ERROR" datatype="VARCHAR" size="5" />

						</DIExpression>

					</DIAssignmentStep>

					<DIAssignmentStep typeId="12" variable="$l_reco_action" >

						<DIExpression isString="true" expr="'Drill to WF level to access the error message. '" >

							<CONSTANT value="Drill to WF level to access the error message. " datatype="VARCHAR" size="47" />

						</DIExpression>

					</DIAssignmentStep>

				</DIIf>

				<DIElse>

					<DIIfStep typeId="2" >

						<DIExpression isString="true" expr="($l_am_failed_count &gt; 0)" >

							<LOGICAL_OP op="GT">

								<VARIABLE_REFERENCE name="$l_am_failed_count" />

								<CONSTANT value="0" datatype="INT" />

							</LOGICAL_OP>

						</DIExpression>

						<DIIf>

							<DIAssignmentStep typeId="12" variable="$l_status" >

								<DIExpression isString="true" expr="'ERROR'" >

									<CONSTANT value="ERROR" datatype="VARCHAR" size="5" />

								</DIExpression>

							</DIAssignmentStep>

							<DIAssignmentStep typeId="12" variable="$l_reco_action" >

								<DIExpression isString="true" expr="($l_reco_action || 'Refer to failed metrics. ')" >

									<CONCAT>

										<VARIABLE_REFERENCE name="$l_reco_action" />

										<CONSTANT value="Refer to failed metrics. " datatype="VARCHAR" size="25" />

									</CONCAT>

								</DIExpression>

							</DIAssignmentStep>

						</DIIf>

						<DIElse>

							<DIIfStep typeId="2" >

								<DIExpression isString="true" expr="($l_am_warning_count &gt; 0)" >

									<LOGICAL_OP op="GT">

										<VARIABLE_REFERENCE name="$l_am_warning_count" />

										<CONSTANT value="0" datatype="INT" />

									</LOGICAL_OP>

								</DIExpression>

								<DIIf>

									<DIAssignmentStep typeId="12" variable="$l_status" >

										<DIExpression isString="true" expr="'WARNING'" >

											<CONSTANT value="WARNING" datatype="VARCHAR" size="7" />

										</DIExpression>

									</DIAssignmentStep>

									<DIAssignmentStep typeId="12" variable="$l_reco_action" >

										<DIExpression isString="true" expr="($l_reco_action || 'Check WARNING metrics. ')" >

											<CONCAT>

												<VARIABLE_REFERENCE name="$l_reco_action" />

												<CONSTANT value="Check WARNING metrics. " datatype="VARCHAR" size="23" />

											</CONCAT>

										</DIExpression>

									</DIAssignmentStep>

								</DIIf>

							</DIIfStep>

						</DIElse>

					</DIIfStep>

				</DIElse>

			</DIIfStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'update JOB_STREAM_STEP_RT&#xA;               set STATUS = {$l_status}, STATE = \'FINISHED\' , END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127), STATUS_INFO = {$l_status_info}, RECO_ACTION = {$l_reco_action}&#xA;               where MD_PROCESS_ID = {$p_process_id}')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="update JOB_STREAM_STEP_RT&#xA;               set STATUS = {$l_status}, STATE = 'FINISHED' , END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127), STATUS_INFO = {$l_status_info}, RECO_ACTION = {$l_reco_action}&#xA;               where MD_PROCESS_ID = {$p_process_id}" datatype="VARCHAR" size="272" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**" datatype="VARCHAR" size="2" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**" datatype="VARCHAR" size="2" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('** The Job {job_name()} is ending')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="** The Job {job_name()} is ending" datatype="VARCHAR" size="33" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**   status: {$l_status}')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**   status: {$l_status}" datatype="VARCHAR" size="24" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**   explanation: {$l_status_info})')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**   explanation: {$l_status_info})" datatype="VARCHAR" size="35" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="********************************" datatype="VARCHAR" size="32" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="********************************" datatype="VARCHAR" size="32" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIReturnStep typeId="30" >

				<DIExpression isString="true" expr=" NULL " >

					<CONSTANT_NULL />
				</DIExpression>

			</DIReturnStep>

		</DISteps>

	</DIScriptFunction>

	<DIScriptFunction name="ABC_ENDWF_FN" typeId="11" >

		<DIUIOptions>

			<DIAttribute name="ui_script_text" value="&#xA;# LOOP THROUGH ALL VALUES IN INPUT PARAMETER TARGET_TABLE FIELD&#xA;# UPDATE EACH VALUE (IN DELIMITED STRING) AS SINGLE RECORD WITH ENDDATE&#xA;# CHECK THAT ABC_ENDWF_FN CANNOT BE CALLED BEFORE ABC_STARTWF_FN&#xA;&#xA;$l_previous_status = sql('DW_ABC_DS','select STATUS from JOB_STREAM_STEP_DETAIL_RT&#xA;                                      where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ');&#xA;&#xA;if ($l_previous_status is null)&#xA;#  sql('DW_ABC_DS','insert into JOB_STREAM_STEP_DETAIL_RT &#xA;#                   (MD_PROCESS_ID, DETAIL_NAME, STATUS, END_TIME)  &#xA;#                   VALUES ({$p_process_id}, {$p_detailname}, \'ERROR\', {sysdate()})');&#x9;&#xD;&#xA;  sql('DW_ABC_DS','insert into JOB_STREAM_STEP_DETAIL_RT &#xA;                   (MD_PROCESS_ID, DETAIL_NAME, STATUS, END_TIME)  &#xA;                   VALUES ({$p_process_id}, {$p_detailname}, \'ERROR\', convert(datetime,{sysdate()},111))');&#x9;&#xA;else&#xA;  begin&#xA;    if ($l_previous_status = 'RUNNING')&#xA;      sql('DW_ABC_DS','update JOB_STREAM_STEP_DETAIL_RT&#xA;                       set STATUS = \'SUCCESS\', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                       where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ');&#xA;&#x9;else&#xA;      sql('DW_ABC_DS','update JOB_STREAM_STEP_DETAIL_RT&#xA;                       set STATUS = \'ERROR\', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                       where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ');&#xA;&#x9;&#xA;  end&#xA;&#xA;ETL_PRINT( '        ** The workflow: [workflow_name()] is ending' );&#xA;ETL_PRINT( '        **   user: ' || system_user_name() );&#xA;ETL_PRINT( '        **   detail name: ' || $p_detailname );&#xA;ETL_PRINT( '        ********************************' );&#xA;ETL_PRINT( '        ********************************' );&#xA;&#xA;return NULL;&#xA;&#xA;&#xA;&#xA;&#xA;  "/>

		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="Acta_user_func_category" value="User_Script_Function"/>

		</DIProperties>

		<DIParameters>
			<DIElement paramType="IN" name="$p_detailname" datatype="VARCHAR" size="100" >

			</DIElement>

			<DIElement paramType="IN" name="$p_process_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

		</DIParameters>

		<DIVariables>
			<DIElement paramType="LOCAL" name="$l_previous_status" datatype="VARCHAR" size="12" >

			</DIElement>

		</DIVariables>

		<DIFunctionReturn datatype="INT" />

		<DISteps>

			<DIAssignmentStep typeId="12" variable="$l_previous_status" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'select STATUS from JOB_STREAM_STEP_DETAIL_RT&#xA;                                      where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="select STATUS from JOB_STREAM_STEP_DETAIL_RT&#xA;                                      where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} " datatype="VARCHAR" size="155" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="($l_previous_status IS  NULL )" >

					<LOGICAL_OP op="IS NULL">

						<VARIABLE_REFERENCE name="$l_previous_status" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="sql('DW_ABC_DS', 'insert into JOB_STREAM_STEP_DETAIL_RT &#xA;                   (MD_PROCESS_ID, DETAIL_NAME, STATUS, END_TIME)  &#xA;                   VALUES ({$p_process_id}, {$p_detailname}, \'ERROR\', convert(datetime,{sysdate()},111))')" >

							<FUNCTION_CALL name="sql"  type="DI" >

								<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

								<CONSTANT value="insert into JOB_STREAM_STEP_DETAIL_RT &#xA;                   (MD_PROCESS_ID, DETAIL_NAME, STATUS, END_TIME)  &#xA;                   VALUES ({$p_process_id}, {$p_detailname}, 'ERROR', convert(datetime,{sysdate()},111))" datatype="VARCHAR" size="211" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

				</DIIf>

				<DIElse>

					<DIIfStep typeId="2" >

						<DIExpression isString="true" expr="($l_previous_status = 'RUNNING')" >

							<LOGICAL_OP op="EQ">

								<VARIABLE_REFERENCE name="$l_previous_status" />

								<CONSTANT value="RUNNING" datatype="VARCHAR" size="7" />

							</LOGICAL_OP>

						</DIExpression>

						<DIIf>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="sql('DW_ABC_DS', 'update JOB_STREAM_STEP_DETAIL_RT&#xA;                       set STATUS = \'SUCCESS\', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                       where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ')" >

									<FUNCTION_CALL name="sql"  type="DI" >

										<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

										<CONSTANT value="update JOB_STREAM_STEP_DETAIL_RT&#xA;                       set STATUS = 'SUCCESS', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                       where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} " datatype="VARCHAR" size="243" />

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIIf>

						<DIElse>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="sql('DW_ABC_DS', 'update JOB_STREAM_STEP_DETAIL_RT&#xA;                       set STATUS = \'ERROR\', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                       where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ')" >

									<FUNCTION_CALL name="sql"  type="DI" >

										<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

										<CONSTANT value="update JOB_STREAM_STEP_DETAIL_RT&#xA;                       set STATUS = 'ERROR', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                       where MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} " datatype="VARCHAR" size="241" />

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIElse>

					</DIIfStep>

				</DIElse>

			</DIIfStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ** The workflow: [workflow_name()] is ending')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ** The workflow: [workflow_name()] is ending" datatype="VARCHAR" size="52" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   user: ' || system_user_name()))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   user: " datatype="VARCHAR" size="19" />

							<FUNCTION_CALL name="system_user_name"  type="DI" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   detail name: ' || $p_detailname))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   detail name: " datatype="VARCHAR" size="26" />

							<VARIABLE_REFERENCE name="$p_detailname" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIReturnStep typeId="30" >

				<DIExpression isString="true" expr=" NULL " >

					<CONSTANT_NULL />
				</DIExpression>

			</DIReturnStep>

		</DISteps>

	</DIScriptFunction>

	<DIScriptFunction name="ABC_FAILJB_FN" typeId="11" >

		<DIUIOptions>

			<DIAttribute name="ui_script_text" value="# IN EVENT THE JOB FAILED, UPDATE JOB RECORD WITH FAIL STATUS&#xA;&#xA;&#xA;if ($p_process_id is null)&#xA;  begin&#xA; &#x9; ETL_PRINT( 'ENTERED THE JOB CATCH BEFORE THE PROCESS_ID COULD BE SET (debug mode specific error)' );&#xA; &#x9; ETL_PRINT( 'AN ERROR RAISED BEFORE the execution of the job start script (this exception will not be recorded by ABC infrastructure)' );&#xA;  end&#xA;else&#xA;  begin&#xA;&#x9; $l_status_text = replace_substr( $p_status_info, '\'','\'\'');&#xA; &#x9; sql('DW_ABC_DS','update JOB_STREAM_STEP_RT&#xA;                 set STATE = \'FINISHED\', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127), STATUS = \'ERROR\', STATUS_INFO = {$l_status_text}&#xA;                 where  MD_PROCESS_ID = {$p_process_id} ');&#xA;&#xA;  end&#xA;&#xA;&#xA;ETL_PRINT( '        ** The Job : [job_name()] FAILED' );&#xA;ETL_PRINT( '        **   user: ' || system_user_name() );&#xA;ETL_PRINT( '        **   status info: ' || $p_status_info );&#xA;ETL_PRINT( '        ********************************' );&#xA;ETL_PRINT( '        ********************************' );&#xA;&#xA;return NULL;&#xA;"/>

		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="Acta_user_func_category" value="User_Script_Function"/>

			<DIAttribute name="Parallelizable" value="no"/>

			<DIAttribute name="isScriptValidationFunction" value="no"/>

		</DIProperties>

		<DIParameters>
			<DIElement paramType="IN" name="$p_process_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

			<DIElement paramType="IN" name="$p_status_info" datatype="VARCHAR" size="400" >

			</DIElement>

		</DIParameters>

		<DIVariables>
			<DIElement paramType="LOCAL" name="$l_status_text" datatype="VARCHAR" size="400" >

			</DIElement>

		</DIVariables>

		<DIFunctionReturn datatype="INT" />

		<DISteps>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="($p_process_id IS  NULL )" >

					<LOGICAL_OP op="IS NULL">

						<VARIABLE_REFERENCE name="$p_process_id" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="etl_print('ENTERED THE JOB CATCH BEFORE THE PROCESS_ID COULD BE SET (debug mode specific error)')" >

							<FUNCTION_CALL name="etl_print"  type="DI" >

								<CONSTANT value="ENTERED THE JOB CATCH BEFORE THE PROCESS_ID COULD BE SET (debug mode specific error)" datatype="VARCHAR" size="84" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="etl_print('AN ERROR RAISED BEFORE the execution of the job start script (this exception will not be recorded by ABC infrastructure)')" >

							<FUNCTION_CALL name="etl_print"  type="DI" >

								<CONSTANT value="AN ERROR RAISED BEFORE the execution of the job start script (this exception will not be recorded by ABC infrastructure)" datatype="VARCHAR" size="120" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

				</DIIf>

				<DIElse>

					<DIAssignmentStep typeId="12" variable="$l_status_text" >

						<DIExpression isString="true" expr="replace_substr($p_status_info, '\'', '\'\'')" >

							<FUNCTION_CALL name="replace_substr"  type="DI" >

								<VARIABLE_REFERENCE name="$p_status_info" />

								<CONSTANT value="'" datatype="VARCHAR" size="1" />

								<CONSTANT value="''" datatype="VARCHAR" size="2" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIAssignmentStep>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="sql('DW_ABC_DS', 'update JOB_STREAM_STEP_RT&#xA;                 set STATE = \'FINISHED\', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127), STATUS = \'ERROR\', STATUS_INFO = {$l_status_text}&#xA;                 where  MD_PROCESS_ID = {$p_process_id} ')" >

							<FUNCTION_CALL name="sql"  type="DI" >

								<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

								<CONSTANT value="update JOB_STREAM_STEP_RT&#xA;                 set STATE = 'FINISHED', END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127), STATUS = 'ERROR', STATUS_INFO = {$l_status_text}&#xA;                 where  MD_PROCESS_ID = {$p_process_id} " datatype="VARCHAR" size="241" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

				</DIElse>

			</DIIfStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ** The Job : [job_name()] FAILED')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ** The Job : [job_name()] FAILED" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   user: ' || system_user_name()))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   user: " datatype="VARCHAR" size="19" />

							<FUNCTION_CALL name="system_user_name"  type="DI" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   status info: ' || $p_status_info))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   status info: " datatype="VARCHAR" size="26" />

							<VARIABLE_REFERENCE name="$p_status_info" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIReturnStep typeId="30" >

				<DIExpression isString="true" expr=" NULL " >

					<CONSTANT_NULL />
				</DIExpression>

			</DIReturnStep>

		</DISteps>

	</DIScriptFunction>

	<DIScriptFunction name="ABC_FAILWF_FN" typeId="11" >

		<DIUIOptions>

			<DIAttribute name="ui_script_text" value="# IN EVENT THE WORKFLOW FAILED, UPDATE WORKFLOW RECORD WITH FAIL STATUS&#xA;&#xA;$l_status_text = replace_substr( $p_status_info, '\'','\'\'');&#xA;&#xA;sql('DW_ABC_DS','update JOB_STREAM_STEP_DETAIL_RT&#xA;                 set STATUS = \'ERROR\', STATUS_INFO = {$l_status_text}, END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                 where  MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ');&#xA;&#xA;ETL_PRINT( '        ** The workflow: [workflow_name()] FAILED' );&#xA;ETL_PRINT( '        **   user: ' || system_user_name() );&#xA;ETL_PRINT( '        **   status info: ' || $p_status_info );&#xA;ETL_PRINT( '        **   detail name: ' || $p_detailname );&#xA;ETL_PRINT( '        ********************************' );&#xA;ETL_PRINT( '        ********************************' );&#xA;&#xA;return NULL;&#xA;"/>

		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="Acta_user_func_category" value="User_Script_Function"/>

		</DIProperties>

		<DIParameters>
			<DIElement paramType="IN" name="$p_detailname" datatype="VARCHAR" size="100" >

			</DIElement>

			<DIElement paramType="IN" name="$p_process_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

			<DIElement paramType="IN" name="$p_status_info" datatype="VARCHAR" size="400" >

			</DIElement>

		</DIParameters>

		<DIVariables>
			<DIElement paramType="LOCAL" name="$l_status_text" datatype="VARCHAR" size="400" >

			</DIElement>

		</DIVariables>

		<DIFunctionReturn datatype="INT" />

		<DISteps>

			<DIAssignmentStep typeId="12" variable="$l_status_text" >

				<DIExpression isString="true" expr="replace_substr($p_status_info, '\'', '\'\'')" >

					<FUNCTION_CALL name="replace_substr"  type="DI" >

						<VARIABLE_REFERENCE name="$p_status_info" />

						<CONSTANT value="'" datatype="VARCHAR" size="1" />

						<CONSTANT value="''" datatype="VARCHAR" size="2" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'update JOB_STREAM_STEP_DETAIL_RT&#xA;                 set STATUS = \'ERROR\', STATUS_INFO = {$l_status_text}, END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                 where  MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} ')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="update JOB_STREAM_STEP_DETAIL_RT&#xA;                 set STATUS = 'ERROR', STATUS_INFO = {$l_status_text}, END_TIME = convert(varchar(25),convert(datetime,getdate(),111),127)&#xA;                 where  MD_PROCESS_ID = {$p_process_id} AND DETAIL_NAME = {$p_detailname} " datatype="VARCHAR" size="262" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ** The workflow: [workflow_name()] FAILED')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ** The workflow: [workflow_name()] FAILED" datatype="VARCHAR" size="49" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   user: ' || system_user_name()))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   user: " datatype="VARCHAR" size="19" />

							<FUNCTION_CALL name="system_user_name"  type="DI" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   status info: ' || $p_status_info))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   status info: " datatype="VARCHAR" size="26" />

							<VARIABLE_REFERENCE name="$p_status_info" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   detail name: ' || $p_detailname))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   detail name: " datatype="VARCHAR" size="26" />

							<VARIABLE_REFERENCE name="$p_detailname" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIReturnStep typeId="30" >

				<DIExpression isString="true" expr=" NULL " >

					<CONSTANT_NULL />
				</DIExpression>

			</DIReturnStep>

		</DISteps>

	</DIScriptFunction>

	<DIScriptFunction name="ABC_STARTJB_FN" typeId="11" >

		<DIUIOptions>

			<DIAttribute name="ui_script_text" value="#$l_job_start_date = sysdate( );&#xD;&#xA;$l_job_start_date = sql('DW_ABC_DS','select getdate()');&#xD;&#xA;$l_job_start_date_vchar = sql('DW_ABC_DS','select convert(varchar(25),convert(datetime,{$l_job_start_date},111),127)');&#xD;&#xA;ETL_PRINT('l_job_start_date_vchar = '|| $l_job_start_date_vchar);&#xD;&#xA;#sql('DW_ABC_DS','select convert(datetime,GETDATE()-5,111)');&#xD;&#xA;#;&#xA;# the job may have been called from the DW scheduler as part of a job stream&#xA;# or it may be launched manually &#xA;# if the job is called as part of a job stream&#xA;#   then its row in DW_ABC_DS.STEP_RUNTIME identified by $G_STEP_RT_ID  has already &#xA;#   been created with a WAITING status&#xA;# else&#xA;#   the job has been launched out of a stream context, it will create its own entry &#xA;#   flagged as MANUAL in DW_ABC_DS.STEP_RUNTIME&#xA;       &#xA;if ($p_debug_mode is null or not $p_debug_mode = 1)&#xA;  begin&#xA;    if ($p_process_id is null)&#xA;      begin&#xA;&#x9;    ETL_PRINT( 'ERROR: $G_PROCESS_ID is not set. If you want to run manually the job, you must set $G_DEBUG_MODE=1' );&#xA;        raise_exception('$G_PROCESS_ID is not set. If you want to run manually the job, you must set $G_DEBUG_MODE=1');&#xA;      end&#xA;    # retrieve the batch id from the STEP_RUNTIME table&#xA;    $p_batch_id = sql('DW_ABC_DS',&#xA;      'SELECT MD_BATCH_ID FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}');&#xA;    if ($p_batch_id is null)&#xA;      begin&#xA;&#x9;    ETL_PRINT( 'ERROR: Process ' || $p_process_id || ' not found in STEP_RUNTIME table.' );&#xA;        raise_exception('process '||$p_process_id||' is not found in STEP_RUNTIME table.');&#xA;      end&#xA;  end&#xA;else&#xA;  begin&#xA;    if ($p_process_id is not null)&#xA;      begin&#xA;&#x9;    ETL_PRINT( 'ERROR: abC usage error detected : $G_DEBUG_MODE is set to ' || $p_debug_mode || ' but $G_PROCESS_ID is not null (set to '||$p_process_id||'). In debug mode Process ID will be allocated by the job and cannot be specified');&#xA;        raise_exception('ERROR: abC usage error detected : $G_DEBUG_MODE is set to ' || $p_debug_mode || ' but $G_PROCESS_ID is not null (set to '||$p_process_id||'). in debug mode Process ID will be allocated by the system and cannot be specified');&#xA;      end&#xA;    # create the job level row in DW_ABC_DS.STEP_RUNTIME note that only a basic row without start dates is created &#xA;  &#x9;# this will be done in code shared for scheduled and manual rows&#xA;&#xD;&#xA;#    sql('DW_ABC_DS','insert into JOB_STREAM_STEP_RT (JOB_NAME, START_TIME, STEP_OWNER, STATE) &#xA;#                     VALUES ({job_name()},{$l_job_start_date}, \'MANUAL\', \'STARTING\')');&#xA;&#xD;&#xA;    sql('DW_ABC_DS','insert into JOB_STREAM_STEP_RT (JOB_NAME, START_TIME, STEP_OWNER, STATE) &#xA;                     VALUES ({job_name()},{$l_job_start_date_vchar}, \'MANUAL\', \'STARTING\')');&#xA;  &#x9;# retrieve the row ID affected by the DB, this ID will be used for any future reference to the STEP_RUNTIME table&#xA;# &#x9;$p_process_id = sql('DW_ABC_DS','SELECT MD_PROCESS_ID from JOB_STREAM_STEP_RT where JOB_NAME = {job_name()} AND START_TIME = {$l_job_start_date} AND STEP_OWNER = \'MANUAL\' AND STATE =  \'STARTING\' ');&#xA;#&#x9;ETL_PRINT( 'After manual launch, the assigned Process ID is: ' || $p_process_id );&#xA;&#xD;&#xA; &#x9;$p_process_id = sql('DW_ABC_DS','SELECT MD_PROCESS_ID from JOB_STREAM_STEP_RT where JOB_NAME = {job_name()} AND START_TIME = {$l_job_start_date_vchar} AND STEP_OWNER = \'MANUAL\' AND STATE =  \'STARTING\' ');&#xA;&#x9;ETL_PRINT( 'After manual launch, the assigned Process ID is: ' || $p_process_id );&#xA;&#x9;# No new batch is created in debug mode, but set 0 to batch-id variable to have a not null value&#xA;&#x9;$p_batch_id = 0;&#xA;  end&#xA;&#xA;&#xA;ETL_PRINT ( 'Process ' || $p_process_id || ' retrieved $p_batch_id = {$p_batch_id}' );&#xA;$l_state = sql('DW_ABC_DS',&#xA;      'SELECT STATE FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}');&#xA;$l_job_name = sql('DW_ABC_DS',&#xA;      'SELECT JOB_NAME FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}');&#xA;&#xA;if ($l_state != 'STARTING' )&#xA;begin&#xA;    sql('DW_ABC_DS','update JOB_STREAM_STEP_RT set STATUS_INFO = \'Tried to run job, but it is not in a runnable state.\' &#xA;         where MD_PROCESS_ID = {$p_process_id}');&#xA;    raise_exception('ERROR: This job is not in a runnable state. This may come from Web Service timeout, the job will not execute and a new one will be trigered by ABC (if not already done)');&#xA;end&#xA;&#xA;if ($l_job_name != job_name() )&#xA;begin&#xA;   # sql('DW_ABC_DS','update JOB_STREAM_STEP_RT set STATUS_INFO = \'This job, {job_name()}, does not match the JOB_NAME of the step you are trying to run ($l_job_name).\' &#xA;   #      where MD_PROCESS_ID = {$p_process_id}');&#xA;    raise_exception('ERROR: This job, {job_name()}, does not match the JOB_NAME of the step you are trying to run (' || $l_job_name || ').');&#xA;end&#xA;&#xA;# now flag the job as running&#xD;&#xA;#sql('DW_ABC_DS','update JOB_STREAM_STEP_RT set STATE = \'RUNNING\',&#xA;#JOB_NAME = {job_name()}, START_TIME = convert(datetime,{$l_job_start_date},111)&#xA;#where MD_PROCESS_ID = {$p_process_id}');&#xA;sql('DW_ABC_DS','update JOB_STREAM_STEP_RT set STATE = \'RUNNING\',&#xA;JOB_NAME = {job_name()}, START_TIME = {$l_job_start_date_vchar}&#xA;where MD_PROCESS_ID = {$p_process_id}');&#xA;&#xA;set_env( 'ETL_DEBUG_MODE', $p_debug_mode );&#xA;set_env( 'ETL_BATCH_ID', $p_batch_id);&#xA;set_env( 'ETL_PROCESS_ID', $p_process_id );&#xA;if ( is_set_env( 'ETL_TRACE_LEVEL' ) != 1 )&#xA;begin&#xA;&#x9;set_env( 'ETL_TRACE_LEVEL', 'ALL' );&#xA;end&#xA;&#xA;ETL_PRINT( '********************************' );&#xA;ETL_PRINT( '********************************' );&#xA;ETL_PRINT( '** The Job {job_name()} is starting' || ifthenelse((get_env('ETL_DEBUG_MODE') = '1'), ' in Debug mode', '') || '.');&#xA;ETL_PRINT( '**' );&#xA;ETL_PRINT( '**' );&#xA;&#xA;return (5);&#xA;"/>

		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="Acta_user_func_category" value="User_Script_Function"/>

		</DIProperties>

		<DIParameters>
			<DIElement paramType="IN_OUT" name="$p_process_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

			<DIElement paramType="IN" name="$p_debug_mode" datatype="INT" >

			</DIElement>

			<DIElement paramType="OUT" name="$p_batch_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

		</DIParameters>

		<DIVariables>
			<DIElement paramType="LOCAL" name="$l_previous_process_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_sqltext" datatype="VARCHAR" size="80" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_job_start_date" datatype="DATETIME" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_state" datatype="VARCHAR" size="30" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_job_name" datatype="VARCHAR" size="60" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_job_start_date_vchar" datatype="VARCHAR" size="25" >

			</DIElement>

		</DIVariables>

		<DIFunctionReturn datatype="DATETIME" />

		<DISteps>

			<DIAssignmentStep typeId="12" variable="$l_job_start_date" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'select getdate()')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="select getdate()" datatype="VARCHAR" size="16" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIAssignmentStep typeId="12" variable="$l_job_start_date_vchar" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'select convert(varchar(25),convert(datetime,{$l_job_start_date},111),127)')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="select convert(varchar(25),convert(datetime,{$l_job_start_date},111),127)" datatype="VARCHAR" size="73" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('l_job_start_date_vchar = ' || $l_job_start_date_vchar))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="l_job_start_date_vchar = " datatype="VARCHAR" size="25" />

							<VARIABLE_REFERENCE name="$l_job_start_date_vchar" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="(($p_debug_mode IS  NULL ) OR&#xA;   ( not ($p_debug_mode = 1)))" >

					<LOGICAL_OP op="OR">

						<LOGICAL_OP op="IS NULL">

							<VARIABLE_REFERENCE name="$p_debug_mode" />

						</LOGICAL_OP>

						<LOGICAL_OP op="NOT">

							<LOGICAL_OP op="EQ">

								<VARIABLE_REFERENCE name="$p_debug_mode" />

								<CONSTANT value="1" datatype="INT" />

							</LOGICAL_OP>

						</LOGICAL_OP>

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIIfStep typeId="2" >

						<DIExpression isString="true" expr="($p_process_id IS  NULL )" >

							<LOGICAL_OP op="IS NULL">

								<VARIABLE_REFERENCE name="$p_process_id" />

							</LOGICAL_OP>

						</DIExpression>

						<DIIf>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="etl_print('ERROR: $G_PROCESS_ID is not set. If you want to run manually the job, you must set $G_DEBUG_MODE=1')" >

									<FUNCTION_CALL name="etl_print"  type="DI" >

										<CONSTANT value="ERROR: $G_PROCESS_ID is not set. If you want to run manually the job, you must set $G_DEBUG_MODE=1" datatype="VARCHAR" size="98" />

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="raise_exception('$G_PROCESS_ID is not set. If you want to run manually the job, you must set $G_DEBUG_MODE=1')" >

									<FUNCTION_CALL name="raise_exception"  type="DI" >

										<CONSTANT value="$G_PROCESS_ID is not set. If you want to run manually the job, you must set $G_DEBUG_MODE=1" datatype="VARCHAR" size="91" />

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIIf>

					</DIIfStep>

					<DIAssignmentStep typeId="12" variable="$p_batch_id" >

						<DIExpression isString="true" expr="sql('DW_ABC_DS', 'SELECT MD_BATCH_ID FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}')" >

							<FUNCTION_CALL name="sql"  type="DI" >

								<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

								<CONSTANT value="SELECT MD_BATCH_ID FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}" datatype="VARCHAR" size="80" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIAssignmentStep>

					<DIIfStep typeId="2" >

						<DIExpression isString="true" expr="($p_batch_id IS  NULL )" >

							<LOGICAL_OP op="IS NULL">

								<VARIABLE_REFERENCE name="$p_batch_id" />

							</LOGICAL_OP>

						</DIExpression>

						<DIIf>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="etl_print((('ERROR: Process ' || $p_process_id) || ' not found in STEP_RUNTIME table.'))" >

									<FUNCTION_CALL name="etl_print"  type="DI" >

										<CONCAT>

											<CONCAT>

												<CONSTANT value="ERROR: Process " datatype="VARCHAR" size="15" />

												<VARIABLE_REFERENCE name="$p_process_id" />

											</CONCAT>

											<CONSTANT value=" not found in STEP_RUNTIME table." datatype="VARCHAR" size="33" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="raise_exception((('process ' || $p_process_id) || ' is not found in STEP_RUNTIME table.'))" >

									<FUNCTION_CALL name="raise_exception"  type="DI" >

										<CONCAT>

											<CONCAT>

												<CONSTANT value="process " datatype="VARCHAR" size="8" />

												<VARIABLE_REFERENCE name="$p_process_id" />

											</CONCAT>

											<CONSTANT value=" is not found in STEP_RUNTIME table." datatype="VARCHAR" size="36" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIIf>

					</DIIfStep>

				</DIIf>

				<DIElse>

					<DIIfStep typeId="2" >

						<DIExpression isString="true" expr="($p_process_id IS NOT  NULL )" >

							<LOGICAL_OP op="IS NOT NULL">

								<VARIABLE_REFERENCE name="$p_process_id" />

							</LOGICAL_OP>

						</DIExpression>

						<DIIf>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="etl_print((((('ERROR: abC usage error detected : $G_DEBUG_MODE is set to ' || $p_debug_mode) || ' but $G_PROCESS_ID is not null (set to ') || $p_process_id) || '). In debug mode Process ID will be allocated by the job and cannot be specified'))" >

									<FUNCTION_CALL name="etl_print"  type="DI" >

										<CONCAT>

											<CONCAT>

												<CONCAT>

													<CONCAT>

														<CONSTANT value="ERROR: abC usage error detected : $G_DEBUG_MODE is set to " datatype="VARCHAR" size="58" />

														<VARIABLE_REFERENCE name="$p_debug_mode" />

													</CONCAT>

													<CONSTANT value=" but $G_PROCESS_ID is not null (set to " datatype="VARCHAR" size="39" />

												</CONCAT>

												<VARIABLE_REFERENCE name="$p_process_id" />

											</CONCAT>

											<CONSTANT value="). In debug mode Process ID will be allocated by the job and cannot be specified" datatype="VARCHAR" size="80" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="raise_exception((((('ERROR: abC usage error detected : $G_DEBUG_MODE is set to ' || $p_debug_mode) || ' but $G_PROCESS_ID is not null (set to ') || $p_process_id) || '). in debug mode Process ID will be allocated by the system and cannot be specified'))" >

									<FUNCTION_CALL name="raise_exception"  type="DI" >

										<CONCAT>

											<CONCAT>

												<CONCAT>

													<CONCAT>

														<CONSTANT value="ERROR: abC usage error detected : $G_DEBUG_MODE is set to " datatype="VARCHAR" size="58" />

														<VARIABLE_REFERENCE name="$p_debug_mode" />

													</CONCAT>

													<CONSTANT value=" but $G_PROCESS_ID is not null (set to " datatype="VARCHAR" size="39" />

												</CONCAT>

												<VARIABLE_REFERENCE name="$p_process_id" />

											</CONCAT>

											<CONSTANT value="). in debug mode Process ID will be allocated by the system and cannot be specified" datatype="VARCHAR" size="83" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIIf>

					</DIIfStep>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="sql('DW_ABC_DS', 'insert into JOB_STREAM_STEP_RT (JOB_NAME, START_TIME, STEP_OWNER, STATE) &#xA;                     VALUES ({job_name()},{$l_job_start_date_vchar}, \'MANUAL\', \'STARTING\')')" >

							<FUNCTION_CALL name="sql"  type="DI" >

								<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

								<CONSTANT value="insert into JOB_STREAM_STEP_RT (JOB_NAME, START_TIME, STEP_OWNER, STATE) &#xA;                     VALUES ({job_name()},{$l_job_start_date_vchar}, 'MANUAL', 'STARTING')" datatype="VARCHAR" size="164" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

					<DIAssignmentStep typeId="12" variable="$p_process_id" >

						<DIExpression isString="true" expr="sql('DW_ABC_DS', 'SELECT MD_PROCESS_ID from JOB_STREAM_STEP_RT where JOB_NAME = {job_name()} AND START_TIME = {$l_job_start_date_vchar} AND STEP_OWNER = \'MANUAL\' AND STATE =  \'STARTING\' ')" >

							<FUNCTION_CALL name="sql"  type="DI" >

								<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

								<CONSTANT value="SELECT MD_PROCESS_ID from JOB_STREAM_STEP_RT where JOB_NAME = {job_name()} AND START_TIME = {$l_job_start_date_vchar} AND STEP_OWNER = 'MANUAL' AND STATE =  'STARTING' " datatype="VARCHAR" size="168" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIAssignmentStep>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="etl_print(('After manual launch, the assigned Process ID is: ' || $p_process_id))" >

							<FUNCTION_CALL name="etl_print"  type="DI" >

								<CONCAT>

									<CONSTANT value="After manual launch, the assigned Process ID is: " datatype="VARCHAR" size="49" />

									<VARIABLE_REFERENCE name="$p_process_id" />

								</CONCAT>

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

					<DIAssignmentStep typeId="12" variable="$p_batch_id" >

						<DIExpression isString="true" expr="0" >

							<CONSTANT value="0" datatype="INT" />

						</DIExpression>

					</DIAssignmentStep>

				</DIElse>

			</DIIfStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print((('Process ' || $p_process_id) || ' retrieved $p_batch_id = {$p_batch_id}'))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONCAT>

								<CONSTANT value="Process " datatype="VARCHAR" size="8" />

								<VARIABLE_REFERENCE name="$p_process_id" />

							</CONCAT>

							<CONSTANT value=" retrieved $p_batch_id = {$p_batch_id}" datatype="VARCHAR" size="38" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIAssignmentStep typeId="12" variable="$l_state" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'SELECT STATE FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="SELECT STATE FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}" datatype="VARCHAR" size="74" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIAssignmentStep typeId="12" variable="$l_job_name" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'SELECT JOB_NAME FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="SELECT JOB_NAME FROM JOB_STREAM_STEP_RT where MD_PROCESS_ID = {$p_process_id}" datatype="VARCHAR" size="77" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="($l_state != 'STARTING')" >

					<LOGICAL_OP op="NE">

						<VARIABLE_REFERENCE name="$l_state" />

						<CONSTANT value="STARTING" datatype="VARCHAR" size="8" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="sql('DW_ABC_DS', 'update JOB_STREAM_STEP_RT set STATUS_INFO = \'Tried to run job, but it is not in a runnable state.\' &#xA;         where MD_PROCESS_ID = {$p_process_id}')" >

							<FUNCTION_CALL name="sql"  type="DI" >

								<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

								<CONSTANT value="update JOB_STREAM_STEP_RT set STATUS_INFO = 'Tried to run job, but it is not in a runnable state.' &#xA;         where MD_PROCESS_ID = {$p_process_id}" datatype="VARCHAR" size="146" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="raise_exception('ERROR: This job is not in a runnable state. This may come from Web Service timeout, the job will not execute and a new one will be trigered by ABC (if not already done)')" >

							<FUNCTION_CALL name="raise_exception"  type="DI" >

								<CONSTANT value="ERROR: This job is not in a runnable state. This may come from Web Service timeout, the job will not execute and a new one will be trigered by ABC (if not already done)" datatype="VARCHAR" size="168" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

				</DIIf>

			</DIIfStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="($l_job_name != job_name())" >

					<LOGICAL_OP op="NE">

						<VARIABLE_REFERENCE name="$l_job_name" />

						<FUNCTION_CALL name="job_name"  type="DI" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="raise_exception((('ERROR: This job, {job_name()}, does not match the JOB_NAME of the step you are trying to run (' || $l_job_name) || ').'))" >

							<FUNCTION_CALL name="raise_exception"  type="DI" >

								<CONCAT>

									<CONCAT>

										<CONSTANT value="ERROR: This job, {job_name()}, does not match the JOB_NAME of the step you are trying to run (" datatype="VARCHAR" size="94" />

										<VARIABLE_REFERENCE name="$l_job_name" />

									</CONCAT>

									<CONSTANT value=")." datatype="VARCHAR" size="2" />

								</CONCAT>

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

				</DIIf>

			</DIIfStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'update JOB_STREAM_STEP_RT set STATE = \'RUNNING\',&#xA;JOB_NAME = {job_name()}, START_TIME = {$l_job_start_date_vchar}&#xA;where MD_PROCESS_ID = {$p_process_id}')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="update JOB_STREAM_STEP_RT set STATE = 'RUNNING',&#xA;JOB_NAME = {job_name()}, START_TIME = {$l_job_start_date_vchar}&#xA;where MD_PROCESS_ID = {$p_process_id}" datatype="VARCHAR" size="150" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="set_env('ETL_DEBUG_MODE', $p_debug_mode)" >

					<FUNCTION_CALL name="set_env"  type="DI" >

						<CONSTANT value="ETL_DEBUG_MODE" datatype="VARCHAR" size="14" />

						<VARIABLE_REFERENCE name="$p_debug_mode" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="set_env('ETL_BATCH_ID', $p_batch_id)" >

					<FUNCTION_CALL name="set_env"  type="DI" >

						<CONSTANT value="ETL_BATCH_ID" datatype="VARCHAR" size="12" />

						<VARIABLE_REFERENCE name="$p_batch_id" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="set_env('ETL_PROCESS_ID', $p_process_id)" >

					<FUNCTION_CALL name="set_env"  type="DI" >

						<CONSTANT value="ETL_PROCESS_ID" datatype="VARCHAR" size="14" />

						<VARIABLE_REFERENCE name="$p_process_id" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="(is_set_env('ETL_TRACE_LEVEL') != 1)" >

					<LOGICAL_OP op="NE">

						<FUNCTION_CALL name="is_set_env"  type="DI" >

							<CONSTANT value="ETL_TRACE_LEVEL" datatype="VARCHAR" size="15" />

						</FUNCTION_CALL>

						<CONSTANT value="1" datatype="INT" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIFunctionCallStep typeId="23" >

						<DIExpression isString="true" expr="set_env('ETL_TRACE_LEVEL', 'ALL')" >

							<FUNCTION_CALL name="set_env"  type="DI" >

								<CONSTANT value="ETL_TRACE_LEVEL" datatype="VARCHAR" size="15" />

								<CONSTANT value="ALL" datatype="VARCHAR" size="3" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIFunctionCallStep>

				</DIIf>

			</DIIfStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="********************************" datatype="VARCHAR" size="32" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="********************************" datatype="VARCHAR" size="32" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print((('** The Job {job_name()} is starting' || ifthenelse((get_env('ETL_DEBUG_MODE') = '1'), ' in Debug mode', '')) || '.'))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONCAT>

								<CONSTANT value="** The Job {job_name()} is starting" datatype="VARCHAR" size="35" />

								<FUNCTION_CALL name="ifthenelse"  type="DI" >

									<LOGICAL_OP op="EQ">

										<FUNCTION_CALL name="get_env"  type="DI" >

											<CONSTANT value="ETL_DEBUG_MODE" datatype="VARCHAR" size="14" />

										</FUNCTION_CALL>

										<CONSTANT value="1" datatype="VARCHAR" size="1" />

									</LOGICAL_OP>

									<CONSTANT value=" in Debug mode" datatype="VARCHAR" size="14" />

									<CONSTANT value="" datatype="VARCHAR" size="0" />

								</FUNCTION_CALL>

							</CONCAT>

							<CONSTANT value="." datatype="VARCHAR" size="1" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**" datatype="VARCHAR" size="2" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('**')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="**" datatype="VARCHAR" size="2" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIReturnStep typeId="30" >

				<DIExpression isString="true" expr="5" >

					<CONSTANT value="5" datatype="INT" />

				</DIExpression>

			</DIReturnStep>

		</DISteps>

	</DIScriptFunction>

	<DIScriptFunction name="ABC_STARTWF_FN" typeId="11" >

		<DIUIOptions>

			<DIAttribute name="ui_script_text" value="ETL_PRINT( '        ********************************' );&#xA;ETL_PRINT( '        ********************************' );&#xA;ETL_PRINT( '        ** The workflow [workflow_name()] is starting' );&#xA;ETL_PRINT( '        **   user: ' || system_user_name() );&#xA;ETL_PRINT( '        **   detail name: ' || $p_detailname );&#xA;&#xA;# DELETE EXISTING WORKFLOW RECORD&#xA;#sql('DW_ABC_DS','delete from RDS_DBA.WFEXECUTION_DW&#xA;#where APPNAME = {$p_appname} AND JOBNAME = {job_name()} AND WFNAME = {workflow_name()}&#xA;#AND TARGET_TABLE = {$l_single_tablename}');&#xA;&#xA;# GET MAX_ID FROM THE WFEXECUTION_DW TABLE&#xA;#$job_ maxkey = sql('DW_ABC_DS', 'SELECT MAX(JOB_ID) FROM RDS_DBA.ETLSYNC_DW');&#xA;#$maxkey = $maxkey + 1;&#xA;&#xA;# INSERT WORKFLOW RECORD&#x9;&#x9;&#xD;&#xA;&#xA;#$l_stepdetail_id = sql('DW_ABC_DS','insert into JOB_STREAM_STEP_DETAIL_RT &#xA;#                (MD_PROCESS_ID, DETAIL_NAME, STATUS, START_TIME)  &#xA;#                VALUES ({$p_process_id}, {$p_detailname}, \'RUNNING\', {sysdate()});&#xA;#&#x9;&#x9;&#x9;&#x9;SELECT SCOPE_IDENTITY() AS NewStepDetailID');&#xA;$l_stepdetail_id = sql('DW_ABC_DS','insert into JOB_STREAM_STEP_DETAIL_RT &#xA;                (MD_PROCESS_ID, DETAIL_NAME, STATUS, START_TIME)  &#xA;                VALUES ({$p_process_id}, {$p_detailname}, \'RUNNING\', convert(varchar(25),convert(datetime,getdate(),111),127));&#xA;&#x9;&#x9;&#x9;&#x9;SELECT SCOPE_IDENTITY() AS NewStepDetailID');&#x9;&#xA;&#xA;set_env(workflow_name(), $l_stepdetail_id);&#xA;&#xA;# print('single_tablename = ' || $l_single_tablename);&#x9;   &#x9;&#xA;&#xA;return null;&#xA;"/>

		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="Acta_user_func_category" value="User_Script_Function"/>

		</DIProperties>

		<DIParameters>
			<DIElement paramType="IN" name="$p_detailname" datatype="VARCHAR" size="100" >

			</DIElement>

			<DIElement paramType="IN" name="$p_process_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

		</DIParameters>

		<DIVariables>
			<DIElement paramType="LOCAL" name="$l_stepdetail_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

		</DIVariables>

		<DIFunctionReturn datatype="INT" />

		<DISteps>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ********************************')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ********************************" datatype="VARCHAR" size="40" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print('        ** The workflow [workflow_name()] is starting')" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONSTANT value="        ** The workflow [workflow_name()] is starting" datatype="VARCHAR" size="53" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   user: ' || system_user_name()))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   user: " datatype="VARCHAR" size="19" />

							<FUNCTION_CALL name="system_user_name"  type="DI" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="etl_print(('        **   detail name: ' || $p_detailname))" >

					<FUNCTION_CALL name="etl_print"  type="DI" >

						<CONCAT>

							<CONSTANT value="        **   detail name: " datatype="VARCHAR" size="26" />

							<VARIABLE_REFERENCE name="$p_detailname" />

						</CONCAT>

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIAssignmentStep typeId="12" variable="$l_stepdetail_id" >

				<DIExpression isString="true" expr="sql('DW_ABC_DS', 'insert into JOB_STREAM_STEP_DETAIL_RT &#xA;                (MD_PROCESS_ID, DETAIL_NAME, STATUS, START_TIME)  &#xA;                VALUES ({$p_process_id}, {$p_detailname}, \'RUNNING\', convert(varchar(25),convert(datetime,getdate(),111),127));&#xA;&#x9;&#x9;&#x9;&#x9;SELECT SCOPE_IDENTITY() AS NewStepDetailID')" >

					<FUNCTION_CALL name="sql"  type="DI" >

						<CONSTANT value="DW_ABC_DS" datatype="VARCHAR" size="9" />

						<CONSTANT value="insert into JOB_STREAM_STEP_DETAIL_RT &#xA;                (MD_PROCESS_ID, DETAIL_NAME, STATUS, START_TIME)  &#xA;                VALUES ({$p_process_id}, {$p_detailname}, 'RUNNING', convert(varchar(25),convert(datetime,getdate(),111),127));&#xA;&#x9;&#x9;&#x9;&#x9;SELECT SCOPE_IDENTITY() AS NewStepDetailID" datatype="VARCHAR" size="280" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIAssignmentStep>

			<DIFunctionCallStep typeId="23" >

				<DIExpression isString="true" expr="set_env(workflow_name(), $l_stepdetail_id)" >

					<FUNCTION_CALL name="set_env"  type="DI" >

						<FUNCTION_CALL name="workflow_name"  type="DI" />

						<VARIABLE_REFERENCE name="$l_stepdetail_id" />

					</FUNCTION_CALL>

				</DIExpression>

			</DIFunctionCallStep>

			<DIReturnStep typeId="30" >

				<DIExpression isString="true" expr=" NULL " >

					<CONSTANT_NULL />
				</DIExpression>

			</DIReturnStep>

		</DISteps>

	</DIScriptFunction>

	<DIScriptFunction name="ETL_PRINT" typeId="11" >

		<DIUIOptions>

			<DIAttribute name="ui_script_text" value="# 1/2 Retrieve contextual information from the environment&#xA;if ( is_set_env( 'ETL_BATCH_ID' ) = 1 )&#xA;begin&#xA;&#x9;$l_batch_id = get_env( 'ETL_BATCH_ID' );&#xA;end&#xA;else&#xA;begin&#xA;&#x9;$l_batch_id = '-';&#xA;end&#xA;&#xA;if ( is_set_env( 'ETL_PROCESS_ID' ) = 1 )&#xA;begin&#xA;&#x9;$l_process_id = get_env( 'ETL_PROCESS_ID' );&#x9;&#xA;end&#xA;else&#xA;begin&#xA;&#x9;$l_process_id = '-';&#x9;&#xA;end&#xA;&#xA;# 2/2 Really print&#xA;if ( get_env('ETL_TRACE_LEVEL') = 'ALL' )&#xA;begin&#xA;&#x9;if ( is_set_env( workflow_name() ) = 1 )&#xA;&#x9;begin&#xA;&#x9;&#x9;$l_stepdetail_id = get_env( workflow_name() );&#xA;&#x9;&#x9;print( '\[Batch:' || $l_batch_id || '\] \[Proc:' || $l_process_id || '\] \[Detail:' || $l_stepdetail_id || '\] ' || $p_MESSAGE );&#xA;&#x9;end&#xA;&#x9;else&#xA;&#x9;begin&#xA;    &#x9;print( '\[Batch:' || $l_batch_id || '\] \[Proc:' || $l_process_id || '\] ' || $p_MESSAGE );&#xA;&#x9;end&#xA;end&#xA;&#xA;return null;&#xA;"/>

		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="Acta_user_func_category" value="User_Script_Function"/>

			<DIAttribute name="Description" value="This function is internal and should NOT be used by application developers.&#xA;&#xA;This function prints the parameter message to the logs with contextual information (batch id, process id) as set by the ABC_STARTJB_FN function.&#xA;&#xA;This trace file is currently the standard Data Services trace file. for instance: C:\Program Files\Business Objects\BusinessObjects Data Services\log\tracelog.txt"/>

			<DIAttribute name="Parallelizable" value="no"/>

			<DIAttribute name="isScriptValidationFunction" value="no"/>

		</DIProperties>

		<DIParameters>
			<DIElement paramType="IN" name="$p_MESSAGE" datatype="VARCHAR" size="256" >

			</DIElement>

		</DIParameters>

		<DIVariables>
			<DIElement paramType="LOCAL" name="$l_batch_id" datatype="INT" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_process_id" datatype="INT" >

			</DIElement>

			<DIElement paramType="LOCAL" name="$l_stepdetail_id" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

		</DIVariables>

		<DIFunctionReturn datatype="INT" />

		<DISteps>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="(is_set_env('ETL_BATCH_ID') = 1)" >

					<LOGICAL_OP op="EQ">

						<FUNCTION_CALL name="is_set_env"  type="DI" >

							<CONSTANT value="ETL_BATCH_ID" datatype="VARCHAR" size="12" />

						</FUNCTION_CALL>

						<CONSTANT value="1" datatype="INT" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIAssignmentStep typeId="12" variable="$l_batch_id" >

						<DIExpression isString="true" expr="get_env('ETL_BATCH_ID')" >

							<FUNCTION_CALL name="get_env"  type="DI" >

								<CONSTANT value="ETL_BATCH_ID" datatype="VARCHAR" size="12" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIAssignmentStep>

				</DIIf>

				<DIElse>

					<DIAssignmentStep typeId="12" variable="$l_batch_id" >

						<DIExpression isString="true" expr="'-'" >

							<CONSTANT value="-" datatype="VARCHAR" size="1" />

						</DIExpression>

					</DIAssignmentStep>

				</DIElse>

			</DIIfStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="(is_set_env('ETL_PROCESS_ID') = 1)" >

					<LOGICAL_OP op="EQ">

						<FUNCTION_CALL name="is_set_env"  type="DI" >

							<CONSTANT value="ETL_PROCESS_ID" datatype="VARCHAR" size="14" />

						</FUNCTION_CALL>

						<CONSTANT value="1" datatype="INT" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIAssignmentStep typeId="12" variable="$l_process_id" >

						<DIExpression isString="true" expr="get_env('ETL_PROCESS_ID')" >

							<FUNCTION_CALL name="get_env"  type="DI" >

								<CONSTANT value="ETL_PROCESS_ID" datatype="VARCHAR" size="14" />

							</FUNCTION_CALL>

						</DIExpression>

					</DIAssignmentStep>

				</DIIf>

				<DIElse>

					<DIAssignmentStep typeId="12" variable="$l_process_id" >

						<DIExpression isString="true" expr="'-'" >

							<CONSTANT value="-" datatype="VARCHAR" size="1" />

						</DIExpression>

					</DIAssignmentStep>

				</DIElse>

			</DIIfStep>

			<DIIfStep typeId="2" >

				<DIExpression isString="true" expr="(get_env('ETL_TRACE_LEVEL') = 'ALL')" >

					<LOGICAL_OP op="EQ">

						<FUNCTION_CALL name="get_env"  type="DI" >

							<CONSTANT value="ETL_TRACE_LEVEL" datatype="VARCHAR" size="15" />

						</FUNCTION_CALL>

						<CONSTANT value="ALL" datatype="VARCHAR" size="3" />

					</LOGICAL_OP>

				</DIExpression>

				<DIIf>

					<DIIfStep typeId="2" >

						<DIExpression isString="true" expr="(is_set_env(workflow_name()) = 1)" >

							<LOGICAL_OP op="EQ">

								<FUNCTION_CALL name="is_set_env"  type="DI" >

									<FUNCTION_CALL name="workflow_name"  type="DI" />

								</FUNCTION_CALL>

								<CONSTANT value="1" datatype="INT" />

							</LOGICAL_OP>

						</DIExpression>

						<DIIf>

							<DIAssignmentStep typeId="12" variable="$l_stepdetail_id" >

								<DIExpression isString="true" expr="get_env(workflow_name())" >

									<FUNCTION_CALL name="get_env"  type="DI" >

										<FUNCTION_CALL name="workflow_name"  type="DI" />

									</FUNCTION_CALL>

								</DIExpression>

							</DIAssignmentStep>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="print(((((((('\\[Batch:' || $l_batch_id) || '\\] \\[Proc:') || $l_process_id) || '\\] \\[Detail:') || $l_stepdetail_id) || '\\] ') || $p_MESSAGE))" >

									<FUNCTION_CALL name="print"  type="DI" >

										<CONCAT>

											<CONCAT>

												<CONCAT>

													<CONCAT>

														<CONCAT>

															<CONCAT>

																<CONCAT>

																	<CONSTANT value="\[Batch:" datatype="VARCHAR" size="8" />

																	<VARIABLE_REFERENCE name="$l_batch_id" />

																</CONCAT>

																<CONSTANT value="\] \[Proc:" datatype="VARCHAR" size="10" />

															</CONCAT>

															<VARIABLE_REFERENCE name="$l_process_id" />

														</CONCAT>

														<CONSTANT value="\] \[Detail:" datatype="VARCHAR" size="12" />

													</CONCAT>

													<VARIABLE_REFERENCE name="$l_stepdetail_id" />

												</CONCAT>

												<CONSTANT value="\] " datatype="VARCHAR" size="3" />

											</CONCAT>

											<VARIABLE_REFERENCE name="$p_MESSAGE" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIIf>

						<DIElse>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="print(((((('\\[Batch:' || $l_batch_id) || '\\] \\[Proc:') || $l_process_id) || '\\] ') || $p_MESSAGE))" >

									<FUNCTION_CALL name="print"  type="DI" >

										<CONCAT>

											<CONCAT>

												<CONCAT>

													<CONCAT>

														<CONCAT>

															<CONSTANT value="\[Batch:" datatype="VARCHAR" size="8" />

															<VARIABLE_REFERENCE name="$l_batch_id" />

														</CONCAT>

														<CONSTANT value="\] \[Proc:" datatype="VARCHAR" size="10" />

													</CONCAT>

													<VARIABLE_REFERENCE name="$l_process_id" />

												</CONCAT>

												<CONSTANT value="\] " datatype="VARCHAR" size="3" />

											</CONCAT>

											<VARIABLE_REFERENCE name="$p_MESSAGE" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIElse>

					</DIIfStep>

				</DIIf>

				<DIElse>

				</DIElse>

			</DIIfStep>

			<DIReturnStep typeId="30" >

				<DIExpression isString="true" expr=" NULL " >

					<CONSTANT_NULL />
				</DIExpression>

			</DIReturnStep>

		</DISteps>

	</DIScriptFunction>

	<DIJob name="CLEAN_DATA_JB" typeId="2" >

		<DIProperties>

			<DIAttribute name="Description" value="The RESTREAM_CLEAN_JB is responsible for cleaning data from target based on restream control table."/>

			<DIAttribute name="Web_Service_Enabled" value="YES"/>

		</DIProperties>

		<DIVariables>

			<DIElement paramType="GLOBAL" name="$G_DEBUG_MODE" datatype="INT" >

			</DIElement>

			<DIElement paramType="GLOBAL" name="$G_FLAG_DELETE" datatype="VARCHAR" size="6" >

			</DIElement>

			<DIElement paramType="GLOBAL" name="$G_FLAG_UPDATE" datatype="VARCHAR" size="6" >

			</DIElement>

			<DIElement paramType="GLOBAL" name="$G_FLAG_NEW" datatype="VARCHAR" size="6" >

			</DIElement>

			<DIElement paramType="GLOBAL" name="$G_MODDATE_LOC" datatype="DATETIME" >

			</DIElement>

			<DIElement paramType="GLOBAL" name="$G_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

			<DIElement paramType="GLOBAL" name="$G_PROCESS_ID" datatype="DECIMAL" precision="19" scale="0" >

			</DIElement>

			<DIElement paramType="GLOBAL" name="$G_TRACE_MODE" datatype="INT" >

			</DIElement>

		</DIVariables>

		<DISteps>

			<DITryStep typeId="13" >

				<DIUIOptions>

					<DIAttribute name="ui_display_name" value="CLEAN_DATA_TRY"/>

				</DIUIOptions>

				<DISteps>

					<DIScript>

						<DIUIOptions>

							<DIAttribute name="ObjectDesc" value="GV_SC is used to set global variables and initiates the metadata tracking for the Job"/>

							<DIAttribute name="ui_display_name" value="START_SC"/>

							<DIAttribute name="ui_script_text" value="ABC_STARTJB_FN($G_PROCESS_ID, $G_DEBUG_MODE, $G_BATCH_ID);&#xD;&#xA;"/>

						</DIUIOptions>

						<DIFunctionCallStep typeId="23" >

							<DIExpression isString="true" expr="abc_startjb_fn($G_PROCESS_ID, $G_DEBUG_MODE, $G_BATCH_ID)" >

								<FUNCTION_CALL name="abc_startjb_fn"  type="DI" >

									<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

									<VARIABLE_REFERENCE name="$G_DEBUG_MODE" />

									<VARIABLE_REFERENCE name="$G_BATCH_ID" />

								</FUNCTION_CALL>

							</DIExpression>

						</DIFunctionCallStep>

					</DIScript>

					<DICallStep typeId="0"  calledObjectType="Workflow" name="CLEAN_DATA_CONT_WF" >

					</DICallStep>

					<DIScript>

						<DIUIOptions>

							<DIAttribute name="ui_display_name" value="END_SC"/>

							<DIAttribute name="ui_script_text" value="# CALL FUNCTION TO TRACK END OF JOB&#xD;&#xA;ABC_ENDJB_FN($G_PROCESS_ID );"/>

						</DIUIOptions>

						<DIFunctionCallStep typeId="23" >

							<DIExpression isString="true" expr="abc_endjb_fn($G_PROCESS_ID)" >

								<FUNCTION_CALL name="abc_endjb_fn"  type="DI" >

									<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

								</FUNCTION_CALL>

							</DIExpression>

						</DIFunctionCallStep>

					</DIScript>

				</DISteps>

				<DICatch typeId="20" errorCode="210101" >

					<DIUIOptions>

						<DIAttribute name="ui_display_name" value="CLEAN_DATA_CATCH"/>

					</DIUIOptions>

					<DISteps>

						<DIScript>

							<DIUIOptions>

								<DIAttribute name="ui_display_name" value="EXTERNAL_FILE_JB_FAIL_SC"/>

								<DIAttribute name="ui_script_text" value="ABC_FAILJB_FN($G_PROCESS_ID, 'Error: \[' || error_number() || '\] ' || error_message());&#xA;"/>

							</DIUIOptions>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="abc_failjb_fn($G_PROCESS_ID, ((('Error: \\[' || error_number()) || '\\] ') || error_message()))" >

									<FUNCTION_CALL name="abc_failjb_fn"  type="DI" >

										<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

										<CONCAT>

											<CONCAT>

												<CONCAT>

													<CONSTANT value="Error: \[" datatype="VARCHAR" size="9" />

													<FUNCTION_CALL name="error_number"  type="DI" />

												</CONCAT>

												<CONSTANT value="\] " datatype="VARCHAR" size="3" />

											</CONCAT>

											<FUNCTION_CALL name="error_message"  type="DI" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

						</DIScript>

					</DISteps>

				</DICatch>

			</DITryStep>

		</DISteps>

		<DIAttributes>

			<DIAttribute name="job_GV_$G_FLAG_DELETE" value="'DEL'"/>

			<DIAttribute name="job_GV_$G_FLAG_NEW" value="'NEW'"/>

			<DIAttribute name="job_GV_$G_FLAG_UPDATE" value="'UPD'"/>

			<DIAttribute name="job_GV_$G_MODDATE_LOC" value="sysdate()"/>

			<DIAttribute name="job_checkpoint_enabled" value="no"/>

			<DIAttribute name="job_collect_statistics" value="no"/>

			<DIAttribute name="job_collect_statistics_monitor" value="no"/>

			<DIAttribute name="job_enable_assemblers" value="yes"/>

			<DIAttribute name="job_enable_audit" value="yes"/>

			<DIAttribute name="job_enable_dataquality" value="yes"/>

			<DIAttribute name="job_export_repo" value="no"/>

			<DIAttribute name="job_export_reports" value="no"/>

			<DIAttribute name="job_isrecoverable" value="no"/>

			<DIAttribute name="job_mode" value="Multi-Process"/>

			<DIAttribute name="job_monitor_sample_rate" value="1000"/>

			<DIAttribute name="job_name" value="CLEAN_DATA_JB"/>

			<DIAttribute name="job_print_version" value="no"/>

			<DIAttribute name="job_testmode_enabled" value="no"/>

			<DIAttribute name="job_trace_abapquery" value="no"/>

			<DIAttribute name="job_trace_all" value="no"/>

			<DIAttribute name="job_trace_ascomm" value="no"/>

			<DIAttribute name="job_trace_assemblers" value="no"/>

			<DIAttribute name="job_trace_audit" value="no"/>

			<DIAttribute name="job_trace_dataflow" value="yes"/>

			<DIAttribute name="job_trace_idoc_file" value="no"/>

			<DIAttribute name="job_trace_memory_loader" value="no"/>

			<DIAttribute name="job_trace_memory_reader" value="no"/>

			<DIAttribute name="job_trace_optimized_dataflow" value="no"/>

			<DIAttribute name="job_trace_parallel_execution" value="no"/>

			<DIAttribute name="job_trace_rfc_function" value="no"/>

			<DIAttribute name="job_trace_row" value="no"/>

			<DIAttribute name="job_trace_script" value="no"/>

			<DIAttribute name="job_trace_session" value="yes"/>

			<DIAttribute name="job_trace_sql_only" value="no"/>

			<DIAttribute name="job_trace_sqlfunctions" value="no"/>

			<DIAttribute name="job_trace_sqlloaders" value="no"/>

			<DIAttribute name="job_trace_sqlreaders" value="no"/>

			<DIAttribute name="job_trace_sqltransforms" value="no"/>

			<DIAttribute name="job_trace_stored_procedure" value="no"/>

			<DIAttribute name="job_trace_table" value="no"/>

			<DIAttribute name="job_trace_table_reader" value="no"/>

			<DIAttribute name="job_trace_transform" value="no"/>

			<DIAttribute name="job_trace_userfunction" value="no"/>

			<DIAttribute name="job_trace_usertransform" value="no"/>

			<DIAttribute name="job_trace_workflow" value="yes"/>

			<DIAttribute name="job_type" value="batch"/>

			<DIAttribute name="job_use_statistics" value="yes"/>

			<DIAttribute name="locale_codepage" value="&lt;default&gt;"/>

			<DIAttribute name="locale_language" value="&lt;default&gt;"/>

			<DIAttribute name="locale_territory" value="&lt;default&gt;"/>

		</DIAttributes>

	</DIJob>

	<DIWorkflow name="CLEAN_DATA_CONT_WF" typeId="2" >

		<DISteps>

			<DICallStep typeId="0"  calledObjectType="Workflow" name="CLEAN_DATA_GEN_CONT_WF" >

			</DICallStep>

		</DISteps>

		<DIAttributes>

			<DIAttribute name="run_once" value="no"/>

			<DIAttribute name="unit_of_recovery" value="no"/>

		</DIAttributes>

	</DIWorkflow>

	<DIWorkflow name="CLEAN_DATA_GEN_CONT_WF" typeId="2" >

		<DIUIOptions>

			<DIAnnotation name="ANNOTATION_0">
				<DIAttribute name="ObjectDesc" value="This workflow is a container for the entities concerned by the CON ETL Step for the external files"/>

			</DIAnnotation>
		</DIUIOptions>

		<DIProperties>

			<DIAttribute name="GeneratorTemplate" value="ENTITY_LEVEL_FOR_PRODUCT_CONTAINER_WF (version 0.13.0-SNAPSHOT)"/>

			<DIAttribute name="GeneratorVersion" value="v1.0"/>

			<DIAttribute name="GenerationDate" value="Fri, 23 Jan 2009 10:49:48"/>

			<DIAttribute name="Description" value="This workflow is a container for the entities concerned by the CON ETL Step for the external files. It integrates the following entities:&#xD;&#xA;  - BUDGET (version 1.0)&#xD;&#xA;  - COSTCATEGORY (version 1.0)&#xD;&#xA;  - MEASURETYPE (version 1.0)&#xD;&#xA;  - STATUS (version 1.0)&#xD;&#xA;"/>

		</DIProperties>

		<DISteps>

			<DICallStep typeId="0"  calledObjectType="Workflow" name="CLEAN_DATA_WF" >

			</DICallStep>

		</DISteps>

		<DIAttributes>

			<DIAttribute name="run_once" value="no"/>

			<DIAttribute name="unit_of_recovery" value="no"/>

		</DIAttributes>

	</DIWorkflow>

	<DIWorkflow name="CLEAN_DATA_WF" typeId="2" >

		<DIProperties>

			<DIAttribute name="Description" value="This Workflow houses the extraction process into a flat file for the AMRIGHTSUSESCOUNT data.&#xA;&#xA;The workflow {Source Table}_FF_WF is the workflow responsible for extracting data from the source table {Source Table} and writing it to the target {Source Table}_FF.  The workflow will be composed of the following objects:&#xA;•&#x9;PM_{Source Table}_TRY – The beginning of the TRY/CATCH block in Data Integrator&#xA;•&#x9;PM_{Source Table}_START_SC – The script that calls the custom function that initiates the metadata tracking for the workflow.&#xA;•&#x9;{Source Table}_FF_DF – The dataflow that extracts the data from the source system.&#xA;•&#x9;{Source Table}_FF_END_SC – The script that calls the custom function that closes the metadata for the workflow.&#xA;•&#x9;{Source Table}_FF_CATCH – The end of the TRY/CATCH block.  It contains the script that calls the custom function which writes metadata if there is a failure."/>

		</DIProperties>

		<DIVariables>

			<DIElement paramType="LOCAL" name="$L_IDENTIFIER" datatype="VARCHAR" size="100" >

			</DIElement>

		</DIVariables>

		<DISteps>

			<DITryStep typeId="13" >

				<DIUIOptions>

					<DIAttribute name="ObjectDesc" value="the Try of the Try / Catch Block"/>

					<DIAttribute name="ui_display_name" value="CLEANDATA_TRY"/>

				</DIUIOptions>

				<DISteps>

					<DIScript>

						<DIUIOptions>

							<DIAttribute name="ObjectDesc" value="The workflow script object {Source Table}_FF_START_SC.  This script calls the custom function STARTWF_FN.  This script opens the metadata records that are part of this process.  The script will also be used to populate the local workflow variables.  The script body will contain:&#xA;&#xA;Script Body&#x9;&#xA;STARTWF_FN('{Source Sys}', '{Source Table}_FF');&#xA;$FLAT_FILE_NAME_LV = ‘{Source Table}_FF’||$ETL_BATCH_ID_GV||.txt&#xA;$AUDIT_FILE_NAME_LV = ‘{Source Table}_AUDIT’||$ETL_BATCH_ID_GV||.txt"/>

							<DIAttribute name="ui_display_name" value="CLEAN_DATA_START_SC"/>

							<DIAttribute name="ui_script_text" value="# ---------------------&#xD;&#xA;#-- ABC ---&#xD;&#xA;$L_IDENTIFIER = workflow_name();&#xD;&#xA;ABC_STARTWF_FN($L_IDENTIFIER, $G_PROCESS_ID);&#xD;&#xA;&#xD;&#xA;#--- END ABC -----&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;&#xD;&#xA;"/>

						</DIUIOptions>

						<DIAssignmentStep typeId="12" variable="$L_IDENTIFIER" >

							<DIExpression isString="true" expr="workflow_name()" >

								<FUNCTION_CALL name="workflow_name"  type="DI" />

							</DIExpression>

						</DIAssignmentStep>

						<DIFunctionCallStep typeId="23" >

							<DIExpression isString="true" expr="abc_startwf_fn($L_IDENTIFIER, $G_PROCESS_ID)" >

								<FUNCTION_CALL name="abc_startwf_fn"  type="DI" >

									<VARIABLE_REFERENCE name="$L_IDENTIFIER" />

									<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

								</FUNCTION_CALL>

							</DIExpression>

						</DIFunctionCallStep>

					</DIScript>

					<DICallStep typeId="1"  calledObjectType="Dataflow" name="CLEAN_DATA_DF" >

					</DICallStep>

					<DIScript>

						<DIUIOptions>

							<DIAttribute name="ObjectDesc" value="The workflow script object {Source Table}_FF_END_SC.  This script calls the custom function ENDWF_FN.  This script closes the metadata records that are part of this process.  The script body will contain:&#xA;&#xA;Script Body&#x9;&#xA;ENDWF_FN('{Source Sys}', '{Source Table}_FF')"/>

							<DIAttribute name="ui_display_name" value="CLEAN_DATA_END_SC"/>

							<DIAttribute name="ui_script_text" value="# this script has been generated from template SubExtWfExitScript &#xD;&#xA;# Template version V1.0&#xD;&#xA;# ---------------------&#xD;&#xA;#--- ABC ---&#xD;&#xA;ABC_ENDWF_FN($L_IDENTIFIER, $G_PROCESS_ID);&#xD;&#xA;&#xD;&#xA;#---- END ABC ----&#xD;&#xA;if($G_TRACE_MODE = 1)&#xD;&#xA;Begin&#xD;&#xA;print(' ');&#xD;&#xA;print(' ');&#xD;&#xA;print('The Workfolw ' || workflow_name() || ' has completed running in Debug mode.');&#xD;&#xA;print(' ');&#xD;&#xA;print(' ');&#xD;&#xA;END"/>

						</DIUIOptions>

						<DIFunctionCallStep typeId="23" >

							<DIExpression isString="true" expr="abc_endwf_fn($L_IDENTIFIER, $G_PROCESS_ID)" >

								<FUNCTION_CALL name="abc_endwf_fn"  type="DI" >

									<VARIABLE_REFERENCE name="$L_IDENTIFIER" />

									<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

								</FUNCTION_CALL>

							</DIExpression>

						</DIFunctionCallStep>

						<DIIfStep typeId="2" >

							<DIExpression isString="true" expr="($G_TRACE_MODE = 1)" >

								<LOGICAL_OP op="EQ">

									<VARIABLE_REFERENCE name="$G_TRACE_MODE" />

									<CONSTANT value="1" datatype="INT" />

								</LOGICAL_OP>

							</DIExpression>

							<DIIf>

								<DIFunctionCallStep typeId="23" >

									<DIExpression isString="true" expr="print(' ')" >

										<FUNCTION_CALL name="print"  type="DI" >

											<CONSTANT value=" " datatype="VARCHAR" size="1" />

										</FUNCTION_CALL>

									</DIExpression>

								</DIFunctionCallStep>

								<DIFunctionCallStep typeId="23" >

									<DIExpression isString="true" expr="print(' ')" >

										<FUNCTION_CALL name="print"  type="DI" >

											<CONSTANT value=" " datatype="VARCHAR" size="1" />

										</FUNCTION_CALL>

									</DIExpression>

								</DIFunctionCallStep>

								<DIFunctionCallStep typeId="23" >

									<DIExpression isString="true" expr="print((('The Workfolw ' || workflow_name()) || ' has completed running in Debug mode.'))" >

										<FUNCTION_CALL name="print"  type="DI" >

											<CONCAT>

												<CONCAT>

													<CONSTANT value="The Workfolw " datatype="VARCHAR" size="13" />

													<FUNCTION_CALL name="workflow_name"  type="DI" />

												</CONCAT>

												<CONSTANT value=" has completed running in Debug mode." datatype="VARCHAR" size="37" />

											</CONCAT>

										</FUNCTION_CALL>

									</DIExpression>

								</DIFunctionCallStep>

								<DIFunctionCallStep typeId="23" >

									<DIExpression isString="true" expr="print(' ')" >

										<FUNCTION_CALL name="print"  type="DI" >

											<CONSTANT value=" " datatype="VARCHAR" size="1" />

										</FUNCTION_CALL>

									</DIExpression>

								</DIFunctionCallStep>

								<DIFunctionCallStep typeId="23" >

									<DIExpression isString="true" expr="print(' ')" >

										<FUNCTION_CALL name="print"  type="DI" >

											<CONSTANT value=" " datatype="VARCHAR" size="1" />

										</FUNCTION_CALL>

									</DIExpression>

								</DIFunctionCallStep>

							</DIIf>

						</DIIfStep>

					</DIScript>

				</DISteps>

				<DICatch typeId="20" errorCode="210101" >

					<DIUIOptions>

						<DIAttribute name="ObjectDesc" value="The workflow catch object {Source Table}_FF_CATCH is the end of the TRY/CATCH block in BO DS.  This catch block will contain the script {Source Table}_FF_FAIL_SC.  This script will call the custom function that is used to write the metadata for failed jobs.  The script will contain the following call.&#xA;&#xA;Script Body&#x9;FAILWF_FN('{Source Sys}', '{Source Table}_FF')"/>

						<DIAttribute name="ui_display_name" value="CLEANDATA_CATCH"/>

					</DIUIOptions>

					<DISteps>

						<DIScript>

							<DIUIOptions>

								<DIAttribute name="ui_display_name" value="BUDGET_EXTERNAL_EXTRACT_FF_FAIL_SC"/>

								<DIAttribute name="ui_script_text" value="#ABC - CALL FUNCTION TO TRACK END OF WORKFLOW&#xD;&#xA;ABC_FAILWF_FN(workflow_name(), $G_PROCESS_ID, 'Error: \[' || error_number() || '\] ' || error_message());&#xD;&#xA;&#xD;&#xA;#----&#xD;&#xA;if( $G_TRACE_MODE = 1)&#xD;&#xA;Begin&#xD;&#xA;print(' ');&#xD;&#xA;print(' ');&#xD;&#xA;print('The Workflow ' || workflow_name() || ' has failed to run.');&#xD;&#xA;print(' ');&#xD;&#xA;print(' ');&#xD;&#xA;End"/>

							</DIUIOptions>

							<DIFunctionCallStep typeId="23" >

								<DIExpression isString="true" expr="abc_failwf_fn(workflow_name(), $G_PROCESS_ID, ((('Error: \\[' || error_number()) || '\\] ') || error_message()))" >

									<FUNCTION_CALL name="abc_failwf_fn"  type="DI" >

										<FUNCTION_CALL name="workflow_name"  type="DI" />

										<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

										<CONCAT>

											<CONCAT>

												<CONCAT>

													<CONSTANT value="Error: \[" datatype="VARCHAR" size="9" />

													<FUNCTION_CALL name="error_number"  type="DI" />

												</CONCAT>

												<CONSTANT value="\] " datatype="VARCHAR" size="3" />

											</CONCAT>

											<FUNCTION_CALL name="error_message"  type="DI" />

										</CONCAT>

									</FUNCTION_CALL>

								</DIExpression>

							</DIFunctionCallStep>

							<DIIfStep typeId="2" >

								<DIExpression isString="true" expr="($G_TRACE_MODE = 1)" >

									<LOGICAL_OP op="EQ">

										<VARIABLE_REFERENCE name="$G_TRACE_MODE" />

										<CONSTANT value="1" datatype="INT" />

									</LOGICAL_OP>

								</DIExpression>

								<DIIf>

									<DIFunctionCallStep typeId="23" >

										<DIExpression isString="true" expr="print(' ')" >

											<FUNCTION_CALL name="print"  type="DI" >

												<CONSTANT value=" " datatype="VARCHAR" size="1" />

											</FUNCTION_CALL>

										</DIExpression>

									</DIFunctionCallStep>

									<DIFunctionCallStep typeId="23" >

										<DIExpression isString="true" expr="print(' ')" >

											<FUNCTION_CALL name="print"  type="DI" >

												<CONSTANT value=" " datatype="VARCHAR" size="1" />

											</FUNCTION_CALL>

										</DIExpression>

									</DIFunctionCallStep>

									<DIFunctionCallStep typeId="23" >

										<DIExpression isString="true" expr="print((('The Workflow ' || workflow_name()) || ' has failed to run.'))" >

											<FUNCTION_CALL name="print"  type="DI" >

												<CONCAT>

													<CONCAT>

														<CONSTANT value="The Workflow " datatype="VARCHAR" size="13" />

														<FUNCTION_CALL name="workflow_name"  type="DI" />

													</CONCAT>

													<CONSTANT value=" has failed to run." datatype="VARCHAR" size="19" />

												</CONCAT>

											</FUNCTION_CALL>

										</DIExpression>

									</DIFunctionCallStep>

									<DIFunctionCallStep typeId="23" >

										<DIExpression isString="true" expr="print(' ')" >

											<FUNCTION_CALL name="print"  type="DI" >

												<CONSTANT value=" " datatype="VARCHAR" size="1" />

											</FUNCTION_CALL>

										</DIExpression>

									</DIFunctionCallStep>

									<DIFunctionCallStep typeId="23" >

										<DIExpression isString="true" expr="print(' ')" >

											<FUNCTION_CALL name="print"  type="DI" >

												<CONSTANT value=" " datatype="VARCHAR" size="1" />

											</FUNCTION_CALL>

										</DIExpression>

									</DIFunctionCallStep>

								</DIIf>

							</DIIfStep>

						</DIScript>

					</DISteps>

				</DICatch>

			</DITryStep>

		</DISteps>

		<DIAttributes>

			<DIAttribute name="run_once" value="no"/>

			<DIAttribute name="unit_of_recovery" value="no"/>

		</DIAttributes>

	</DIWorkflow>

	<DIDataflow name="CLEAN_DATA_DF" typeId="1" >

		<DITransforms>

			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="QRY_JOIN_METADATA"/>

					<DIAttribute name="ui_acta_from_schema_1" value="CONTROL_1"/>

					<DIAttribute name="ui_display_name" value="QRY_CLEANDATA"/>

					<DIAttribute name="ui_where_text" value="CONTROL_1.ENTITY_NAME = QRY_JOIN_METADATA.DWID AND CONTROL_1.EXTRACT_TSNP_FLAG = 'Y' AND CONTROL_1.STATUS = 'NEW' AND CONTROL_1.MD_BATCH_ID = $G_BATCH_ID"/>

				</DIUIOptions>

				<DISchema name="QRY_CLEANDATA" >

					<DIElement name="RESTREAM_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONTROL_1.RESTREAM_BATCH_ID&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="ENTITY_NAME" datatype="VARCHAR" size="100" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONTROL_1.ENTITY_NAME&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="TARGET_NAME" datatype="VARCHAR" size="300" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_JOIN_METADATA.TARGET_NAME&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="STATUS" datatype="VARCHAR" size="30" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONTROL_1.STATUS&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="EXTRACT_TSNP_FLAG" datatype="VARCHAR" size="1" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONTROL_1.EXTRACT_TSNP_FLAG&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect isDistinct="true">

					<DIProjection>

						<DIExpression isString="true" expr="CONTROL_1.RESTREAM_BATCH_ID" >

							<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="RESTREAM_BATCH_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="CONTROL_1.ENTITY_NAME" >

							<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="ENTITY_NAME" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_JOIN_METADATA.TARGET_NAME" >

							<COLUMN_REFERENCE  qualifier1="QRY_JOIN_METADATA" column="TARGET_NAME" />

						</DIExpression>

						<DIExpression isString="true" expr="CONTROL_1.STATUS" >

							<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="STATUS" />

						</DIExpression>

						<DIExpression isString="true" expr="CONTROL_1.EXTRACT_TSNP_FLAG" >

							<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="EXTRACT_TSNP_FLAG" />

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="QRY_JOIN_METADATA" />

						<DITableSpec name="CONTROL_1" />

					</DIFrom>

					<DIWhere>

						<DIExpression isString="true" expr="((((CONTROL_1.ENTITY_NAME = QRY_JOIN_METADATA.DWID) AND&#xA;   (CONTROL_1.EXTRACT_TSNP_FLAG = 'Y')) AND&#xA;   (CONTROL_1.STATUS = 'NEW')) AND&#xA;   (CONTROL_1.MD_BATCH_ID = $G_BATCH_ID))" >

							<LOGICAL_OP op="AND">

								<LOGICAL_OP op="AND">

									<LOGICAL_OP op="AND">

										<LOGICAL_OP op="EQ">

											<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="ENTITY_NAME" />

											<COLUMN_REFERENCE  qualifier1="QRY_JOIN_METADATA" column="DWID" />

										</LOGICAL_OP>

										<LOGICAL_OP op="EQ">

											<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="EXTRACT_TSNP_FLAG" />

											<CONSTANT value="Y" datatype="VARCHAR" size="1" />

										</LOGICAL_OP>

									</LOGICAL_OP>

									<LOGICAL_OP op="EQ">

										<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="STATUS" />

										<CONSTANT value="NEW" datatype="VARCHAR" size="3" />

									</LOGICAL_OP>

								</LOGICAL_OP>

								<LOGICAL_OP op="EQ">

									<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="MD_BATCH_ID" />

									<VARIABLE_REFERENCE name="$G_BATCH_ID" />

								</LOGICAL_OP>

							</LOGICAL_OP>

						</DIExpression>

					</DIWhere>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="CONSOLIDATIONMODEL"/>

					<DIAttribute name="ui_acta_from_schema_1" value="MRG_TARGET"/>

					<DIAttribute name="ui_display_name" value="QRY_JOIN_METADATA"/>

					<DIAttribute name="ui_where_text" value="(MRG_TARGET.CONSOLIDATION_HJID = CONSOLIDATIONMODEL.HJID)"/>

				</DIUIOptions>

				<DISchema name="QRY_JOIN_METADATA" >

					<DIElement name="DWID" datatype="VARCHAR" size="25" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONSOLIDATIONMODEL.DWID&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="TARGET_NAME" datatype="VARCHAR" size="300" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="MRG_TARGET.TARGET_NAME"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect>

					<DIProjection>

						<DIExpression isString="true" expr="CONSOLIDATIONMODEL.DWID" >

							<COLUMN_REFERENCE  qualifier1="CONSOLIDATIONMODEL" column="DWID" />

						</DIExpression>

						<DIExpression isString="true" expr="MRG_TARGET.TARGET_NAME" >

							<COLUMN_REFERENCE  qualifier1="MRG_TARGET" column="TARGET_NAME" />

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="CONSOLIDATIONMODEL" />

						<DITableSpec name="MRG_TARGET" />

					</DIFrom>

					<DIWhere>

						<DIExpression isString="true" expr="(MRG_TARGET.CONSOLIDATION_HJID = CONSOLIDATIONMODEL.HJID)" >

							<LOGICAL_OP op="EQ">

								<COLUMN_REFERENCE  qualifier1="MRG_TARGET" column="CONSOLIDATION_HJID" />

								<COLUMN_REFERENCE  qualifier1="CONSOLIDATIONMODEL" column="HJID" />

							</LOGICAL_OP>

						</DIExpression>

					</DIWhere>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DIDatabaseTableSource typeId="22" datastoreName="DW_METADATA_DS" ownerName="DW_METADATA_ALIAS" tableName="CONSOLIDATIONMODEL" >

				<DIOutputView name="CONSOLIDATIONMODEL"  />

				<DIAttributes>

					<DIAttribute name="array_fetch_size" value="1000"/>

					<DIAttribute name="cache" value="yes"/>

					<DIAttribute name="connection_port" value="no"/>

					<DIAttribute name="enable_partitioning" value="no"/>

					<DIAttribute name="name" value="DW_METADATA_DS"/>

					<DIAttribute name="package_size" value="0"/>

					<DIAttribute name="reader_is_DB2CDC_table" value="no"/>

					<DIAttribute name="reader_overflow_file" value="overflow_file"/>

					<DIAttribute name="reader_template_table" value="no"/>

					<DIAttribute name="reader_use_overflow_file" value="no"/>

					<DIAttribute name="reader_use_trex_transaction" value="no"/>

					<DIAttribute name="table_weight" value="0"/>

				</DIAttributes>

			</DIDatabaseTableSource>



			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="QRY_CLEANDATA"/>

					<DIAttribute name="ui_display_name" value="QRY_CALL_SP"/>

				</DIUIOptions>

				<DISchema name="QRY_CALL_SP" >

					<DIElement name="RESTREAM_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CLEANDATA.RESTREAM_BATCH_ID"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="ENTITY_NAME" datatype="VARCHAR" size="100" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CLEANDATA.ENTITY_NAME"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="TARGET_NAME" datatype="VARCHAR" size="300" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CLEANDATA.TARGET_NAME"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="DW_DS.DW_ALIAS.CLEANDATA_TRG" hasNestedSchema="true">

						<DINestedSchema type="RFCSchema">

							<DIElement name="CLEAN_TRG_RETCODE" datatype="INT" >

							</DIElement>

						</DINestedSchema>

					</DIElement>

					<DIElement name="DWSTAGING_DS.DWSTAGING_ALIAS.CLEANDATA_STG" hasNestedSchema="true">

						<DINestedSchema type="RFCSchema">

							<DIElement name="CLEAN_STG_RETCODE" datatype="INT" >

							</DIElement>

						</DINestedSchema>

					</DIElement>

					<DIElement name="STATUS" datatype="VARCHAR" size="30" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CLEANDATA.STATUS"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="EXTRACT_TSNP_FLAG" datatype="VARCHAR" size="1" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CLEANDATA.EXTRACT_TSNP_FLAG"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect>

					<DIProjection>

						<DIExpression isString="true" expr="QRY_CLEANDATA.RESTREAM_BATCH_ID" >

							<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="RESTREAM_BATCH_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_CLEANDATA.ENTITY_NAME" >

							<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="ENTITY_NAME" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_CLEANDATA.TARGET_NAME" >

							<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="TARGET_NAME" />

						</DIExpression>

						<DIExpression isString="true" expr="__AL_STORED_PROCEDURE DW_DS.DW_ALIAS.CLEANDATA_TRG[AL_SP_RETURN](QRY_CLEANDATA.TARGET_NAME, QRY_CLEANDATA.RESTREAM_BATCH_ID)[ENTITYNAME,BATCHEID]" >

							<FUNCTION_CALL name="CLEANDATA_TRG"  type="StoredProcedure" owner="DW_ALIAS" datastore="DW_DS" >

								<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="TARGET_NAME" />

								<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="RESTREAM_BATCH_ID" />

							</FUNCTION_CALL>

						</DIExpression>

						<DIExpression isString="true" expr="__AL_STORED_PROCEDURE DWSTAGING_DS.DWSTAGING_ALIAS.CLEANDATA_STG[AL_SP_RETURN](QRY_CLEANDATA.ENTITY_NAME, QRY_CLEANDATA.RESTREAM_BATCH_ID)[ENTITYNAME,BATCHEID]" >

							<FUNCTION_CALL name="CLEANDATA_STG"  type="StoredProcedure" owner="DWSTAGING_ALIAS" datastore="DWSTAGING_DS" >

								<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="ENTITY_NAME" />

								<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="RESTREAM_BATCH_ID" />

							</FUNCTION_CALL>

						</DIExpression>

						<DIExpression isString="true" expr="QRY_CLEANDATA.STATUS" >

							<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="STATUS" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_CLEANDATA.EXTRACT_TSNP_FLAG" >

							<COLUMN_REFERENCE  qualifier1="QRY_CLEANDATA" column="EXTRACT_TSNP_FLAG" />

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="QRY_CLEANDATA" />

					</DIFrom>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DIDatabaseTableSource typeId="22" datastoreName="DW_METADATA_DS" ownerName="DW_METADATA_ALIAS" tableName="CONSOLIDATIONFACTREF" >

				<DIOutputView name="CONSOLIDATIONFACTREF"  />

				<DIAttributes>

					<DIAttribute name="array_fetch_size" value="1000"/>

					<DIAttribute name="cache" value="yes"/>

					<DIAttribute name="connection_port" value="no"/>

					<DIAttribute name="enable_partitioning" value="no"/>

					<DIAttribute name="name" value="DW_METADATA_DS"/>

					<DIAttribute name="package_size" value="0"/>

					<DIAttribute name="reader_is_DB2CDC_table" value="no"/>

					<DIAttribute name="reader_overflow_file" value="overflow_file"/>

					<DIAttribute name="reader_template_table" value="no"/>

					<DIAttribute name="reader_use_overflow_file" value="no"/>

					<DIAttribute name="reader_use_trex_transaction" value="no"/>

					<DIAttribute name="table_weight" value="0"/>

				</DIAttributes>

			</DIDatabaseTableSource>



			<DITransformCall typeId="111" name="Merge" >

				<DIUIOptions>

					<DIAttribute name="ui_display_name" value="MRG_TARGET"/>

				</DIUIOptions>

				<DIInputView name="QRY_EXT_DIM"  />

				<DIInputView name="QRY_EXT_FACT"  />

				<DISchema name="MRG_TARGET" >

				</DISchema>

			</DITransformCall>

			<DIDatabaseTableSource typeId="22" datastoreName="DW_METADATA_DS" ownerName="DW_METADATA_ALIAS" tableName="CONSOLIDATIONDIMREF" >

				<DIOutputView name="CONSOLIDATIONDIMREF_1"  />

				<DIAttributes>

					<DIAttribute name="array_fetch_size" value="1000"/>

					<DIAttribute name="cache" value="yes"/>

					<DIAttribute name="connection_port" value="no"/>

					<DIAttribute name="enable_partitioning" value="no"/>

					<DIAttribute name="name" value="DW_METADATA_DS"/>

					<DIAttribute name="package_size" value="0"/>

					<DIAttribute name="reader_is_DB2CDC_table" value="no"/>

					<DIAttribute name="reader_overflow_file" value="overflow_file"/>

					<DIAttribute name="reader_template_table" value="no"/>

					<DIAttribute name="reader_use_overflow_file" value="no"/>

					<DIAttribute name="reader_use_trex_transaction" value="no"/>

					<DIAttribute name="table_weight" value="0"/>

				</DIAttributes>

			</DIDatabaseTableSource>



			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="CONSOLIDATIONDIMREF_1"/>

					<DIAttribute name="ui_display_name" value="QRY_EXT_DIM"/>

				</DIUIOptions>

				<DISchema name="QRY_EXT_DIM" >

					<DIElement name="CONSOLIDATION_HJID" datatype="DECIMAL" precision="19" scale="0" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONSOLIDATIONDIMREF_1.CONSOLIDATION_HJID"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="TARGET_NAME" datatype="VARCHAR" size="300" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONSOLIDATIONDIMREF_1.DIMENSIONIDENTIFIER||'_DIM'"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect>

					<DIProjection>

						<DIExpression isString="true" expr="CONSOLIDATIONDIMREF_1.CONSOLIDATION_HJID" >

							<COLUMN_REFERENCE  qualifier1="CONSOLIDATIONDIMREF_1" column="CONSOLIDATION_HJID" />

						</DIExpression>

						<DIExpression isString="true" expr="(CONSOLIDATIONDIMREF_1.DIMENSIONIDENTIFIER || '_DIM')" >

							<CONCAT>

								<COLUMN_REFERENCE  qualifier1="CONSOLIDATIONDIMREF_1" column="DIMENSIONIDENTIFIER" />

								<CONSTANT value="_DIM" datatype="VARCHAR" size="4" />

							</CONCAT>

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="CONSOLIDATIONDIMREF_1" />

					</DIFrom>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="CONSOLIDATIONFACTREF"/>

					<DIAttribute name="ui_display_name" value="QRY_EXT_FACT"/>

				</DIUIOptions>

				<DISchema name="QRY_EXT_FACT" >

					<DIElement name="CONSOLIDATION_HJID" datatype="DECIMAL" precision="19" scale="0" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONSOLIDATIONFACTREF.CONSOLIDATION_HJID"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="TARGET_NAME" datatype="VARCHAR" size="300" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONSOLIDATIONFACTREF.FACTIDENTIFIER||'_FACT'"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect>

					<DIProjection>

						<DIExpression isString="true" expr="CONSOLIDATIONFACTREF.CONSOLIDATION_HJID" >

							<COLUMN_REFERENCE  qualifier1="CONSOLIDATIONFACTREF" column="CONSOLIDATION_HJID" />

						</DIExpression>

						<DIExpression isString="true" expr="(CONSOLIDATIONFACTREF.FACTIDENTIFIER || '_FACT')" >

							<CONCAT>

								<COLUMN_REFERENCE  qualifier1="CONSOLIDATIONFACTREF" column="FACTIDENTIFIER" />

								<CONSTANT value="_FACT" datatype="VARCHAR" size="5" />

							</CONCAT>

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="CONSOLIDATIONFACTREF" />

					</DIFrom>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="QRY_CALL_SP"/>

					<DIAttribute name="ui_display_name" value="QRY_FILTER"/>

					<DIAttribute name="ui_where_text" value="QRY_CALL_SP.CLEAN_STG_RETCODE =0 AND QRY_CALL_SP.CLEAN_TRG_RETCODE = 0"/>

				</DIUIOptions>

				<DISchema name="QRY_FILTER" >

					<DIElement name="ENTITY_NAME" datatype="VARCHAR" size="100" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CALL_SP.ENTITY_NAME"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="RESTREAM_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CALL_SP.RESTREAM_BATCH_ID"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="EXTRACT_TSNP_FLAG" datatype="VARCHAR" size="1" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_CALL_SP.EXTRACT_TSNP_FLAG"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect isDistinct="true">

					<DIProjection>

						<DIExpression isString="true" expr="QRY_CALL_SP.ENTITY_NAME" >

							<COLUMN_REFERENCE  qualifier1="QRY_CALL_SP" column="ENTITY_NAME" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_CALL_SP.RESTREAM_BATCH_ID" >

							<COLUMN_REFERENCE  qualifier1="QRY_CALL_SP" column="RESTREAM_BATCH_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_CALL_SP.EXTRACT_TSNP_FLAG" >

							<COLUMN_REFERENCE  qualifier1="QRY_CALL_SP" column="EXTRACT_TSNP_FLAG" />

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="QRY_CALL_SP" />

					</DIFrom>

					<DIWhere>

						<DIExpression isString="true" expr="((QRY_CALL_SP.CLEAN_STG_RETCODE = 0) AND&#xA;   (QRY_CALL_SP.CLEAN_TRG_RETCODE = 0))" >

							<LOGICAL_OP op="AND">

								<LOGICAL_OP op="EQ">

									<COLUMN_REFERENCE  qualifier1="QRY_CALL_SP" column="CLEAN_STG_RETCODE" />

									<CONSTANT value="0" datatype="INT" />

								</LOGICAL_OP>

								<LOGICAL_OP op="EQ">

									<COLUMN_REFERENCE  qualifier1="QRY_CALL_SP" column="CLEAN_TRG_RETCODE" />

									<CONSTANT value="0" datatype="INT" />

								</LOGICAL_OP>

							</LOGICAL_OP>

						</DIExpression>

					</DIWhere>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="QRY_FILTER"/>

					<DIAttribute name="ui_display_name" value="QRY_SETVALUE"/>

				</DIUIOptions>

				<DISchema name="QRY_SETVALUE" >

					<DIElement name="ENTITY_NAME" datatype="VARCHAR" size="100" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_FILTER.ENTITY_NAME"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="RESTREAM_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_FILTER.RESTREAM_BATCH_ID"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="EXTRACT_TSNP_FLAG" datatype="VARCHAR" size="1" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="QRY_FILTER.EXTRACT_TSNP_FLAG"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="STATUS" datatype="VARCHAR" size="30" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="'PROCESSED'&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="MD_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="$G_BATCH_ID &#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="MD_PROCESS_ID" datatype="DECIMAL" precision="19" scale="0" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="$G_PROCESS_ID&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="MODIFIEDDATETIME" datatype="DATETIME" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="sysdate()&#xD;&#xA; &#xD;&#xA;"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect>

					<DIProjection>

						<DIExpression isString="true" expr="QRY_FILTER.ENTITY_NAME" >

							<COLUMN_REFERENCE  qualifier1="QRY_FILTER" column="ENTITY_NAME" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_FILTER.RESTREAM_BATCH_ID" >

							<COLUMN_REFERENCE  qualifier1="QRY_FILTER" column="RESTREAM_BATCH_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="QRY_FILTER.EXTRACT_TSNP_FLAG" >

							<COLUMN_REFERENCE  qualifier1="QRY_FILTER" column="EXTRACT_TSNP_FLAG" />

						</DIExpression>

						<DIExpression isString="true" expr="'PROCESSED'" >

							<CONSTANT value="PROCESSED" datatype="VARCHAR" size="9" />

						</DIExpression>

						<DIExpression isString="true" expr="$G_BATCH_ID" >

							<VARIABLE_REFERENCE name="$G_BATCH_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="$G_PROCESS_ID" >

							<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="sysdate()" >

							<FUNCTION_CALL name="sysdate"  type="DI" />

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="QRY_FILTER" />

					</DIFrom>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DITransformCall typeId="111" name="Map_Operation" >

				<DIInputView name="Merge"  />

				<DISchema name="Map_Operation" >

				</DISchema>

				<DIAttributes>

					<DIAttribute name="delete" value="discard"/>

					<DIAttribute name="insert" value="discard"/>

					<DIAttribute name="normal" value="update"/>

					<DIAttribute name="update" value="discard"/>

				</DIAttributes>

			</DITransformCall>

			<DIDatabaseTableTarget typeID="11" bulkLoader="false" datastoreName="SRCSTAGING_DS" ownerName="SRCSTAGING_ALIAS" tableName="CONTROL" >

				<DIInputView name="Map_Operation"  />

				<DIAttributes>

					<DIAttribute name="LDRLiveLoad" value="no"/>

					<DIAttribute name="Table_Type" value="TABLE"/>

					<DIAttribute name="connection_port" value="no"/>

					<DIAttribute name="ldr_configuration_enabled" value="yes"/>

					<DIAttribute name="ldr_configurations" hasNestedXMLTree ="true">


						<LDRConfigurations>
							<LDRConfiguration database_type="Microsoft_SQL_Server" database_version="Microsoft SQL Server 2005">
								<auto_correct_using_merge>Yes</auto_correct_using_merge>
								<bulk_ldr_all_rows></bulk_ldr_all_rows>
								<bulk_ldr_max_errors></bulk_ldr_max_errors>
								<bulk_ldr_rows_per_commit></bulk_ldr_rows_per_commit>
								<enable_partitioning>no</enable_partitioning>
								<ignore_column_case>yes</ignore_column_case>
								<ignore_columns_null>No</ignore_columns_null>
								<ignore_columns_value></ignore_columns_value>
								<loader_auto_correct>no</loader_auto_correct>
								<loader_bulk_load>0</loader_bulk_load>
								<loader_compare_column>compare_by_name</loader_compare_column>
								<loader_delete_map></loader_delete_map>
								<loader_drop_and_create_table>no</loader_drop_and_create_table>
								<loader_insert_map></loader_insert_map>
								<loader_load_choice>append</loader_load_choice>
								<loader_num_parallel_loaders>1</loader_num_parallel_loaders>
								<loader_overflow_file>SRCSTAGING_DS_SRCSTAGING_ALIAS_CONTROL.txt</loader_overflow_file>
								<loader_post_load1></loader_post_load1>
								<loader_pre_load1></loader_pre_load1>
								<loader_quote_names>0</loader_quote_names>
								<loader_transactional>no</loader_transactional>
								<loader_transactional_order>0</loader_transactional_order>
								<loader_update_key_columns>no</loader_update_key_columns>
								<loader_update_map></loader_update_map>
								<loader_use_user_defined_keys>yes</loader_use_user_defined_keys>
								<loader_xact_size>1000</loader_xact_size>
								<mssql_bulk_ldr_mode>append</mssql_bulk_ldr_mode>
								<mssql_bulk_ldr_packet_size>4</mssql_bulk_ldr_packet_size>
								<overflow_file_format>Write data</overflow_file_format>
								<overriding_substitution_parameter_for_tablename></overriding_substitution_parameter_for_tablename>
								<use_overflow_file>no</use_overflow_file>
								<use_unicode_varchar>no</use_unicode_varchar>
								<sql_text></sql_text>
							</LDRConfiguration>
						</LDRConfigurations>

					</DIAttribute>

					<DIAttribute name="loader_template_table" value="no"/>

					<DIAttribute name="name" value="SRCSTAGING_DS"/>

					<DIAttribute name="use_unicode_varchar" value="no"/>

				</DIAttributes>

			</DIDatabaseTableTarget>



			<DIDatabaseTableSource typeId="22" datastoreName="SRCSTAGING_DS" ownerName="SRCSTAGING_ALIAS" tableName="CONTROL" >

				<DIOutputView name="CONTROL_1"  />

				<DIAttributes>

					<DIAttribute name="array_fetch_size" value="1000"/>

					<DIAttribute name="cache" value="yes"/>

					<DIAttribute name="connection_port" value="no"/>

					<DIAttribute name="enable_partitioning" value="no"/>

					<DIAttribute name="name" value="SRCSTAGING_DS"/>

					<DIAttribute name="package_size" value="0"/>

					<DIAttribute name="reader_is_DB2CDC_table" value="no"/>

					<DIAttribute name="reader_overflow_file" value="overflow_file"/>

					<DIAttribute name="reader_template_table" value="no"/>

					<DIAttribute name="reader_use_overflow_file" value="no"/>

					<DIAttribute name="reader_use_trex_transaction" value="no"/>

					<DIAttribute name="table_weight" value="0"/>

				</DIAttributes>

			</DIDatabaseTableSource>



			<DIQuery typeId="122"  >

				<DIUIOptions>

					<DIAttribute name="ui_acta_from_schema_0" value="QRY_JOIN_METADATA"/>

					<DIAttribute name="ui_acta_from_schema_1" value="CONTROL_1"/>

					<DIAttribute name="ui_display_name" value="QRY_NON_CLEANDATA"/>

					<DIAttribute name="ui_where_text" value="CONTROL_1.ENTITY_NAME = QRY_JOIN_METADATA.DWID AND CONTROL_1.EXTRACT_TSNP_FLAG = 'N' AND CONTROL_1.STATUS = 'NEW' AND CONTROL_1.MD_BATCH_ID = $G_BATCH_ID"/>

				</DIUIOptions>

				<DISchema name="QRY_NON_CLEANDATA" >

					<DIElement name="ENTITY_NAME" datatype="VARCHAR" size="100" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONTROL_1.ENTITY_NAME"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="RESTREAM_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONTROL_1.RESTREAM_BATCH_ID"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="EXTRACT_TSNP_FLAG" datatype="VARCHAR" size="1" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="CONTROL_1.EXTRACT_TSNP_FLAG"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="STATUS" datatype="VARCHAR" size="30" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="'PROCESSED WITHOUT CLEANDATA'"/>

						</DIAttributes>

					</DIElement>

					<DIElement name="MD_BATCH_ID" datatype="DECIMAL" precision="19" scale="0" key="true" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="$G_BATCH_ID "/>

						</DIAttributes>

					</DIElement>

					<DIElement name="MD_PROCESS_ID" datatype="DECIMAL" precision="19" scale="0" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="$G_PROCESS_ID "/>

						</DIAttributes>

					</DIElement>

					<DIElement name="MODIFIEDDATETIME" datatype="DATETIME" >

						<DIAttributes>

							<DIAttribute name="Description" value=""/>

							<DIAttribute name="ui_mapping_text" value="sysdate()"/>

						</DIAttributes>

					</DIElement>

				</DISchema>

				<DISelect>

					<DIProjection>

						<DIExpression isString="true" expr="CONTROL_1.ENTITY_NAME" >

							<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="ENTITY_NAME" />

						</DIExpression>

						<DIExpression isString="true" expr="CONTROL_1.RESTREAM_BATCH_ID" >

							<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="RESTREAM_BATCH_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="CONTROL_1.EXTRACT_TSNP_FLAG" >

							<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="EXTRACT_TSNP_FLAG" />

						</DIExpression>

						<DIExpression isString="true" expr="'PROCESSED WITHOUT CLEANDATA'" >

							<CONSTANT value="PROCESSED WITHOUT CLEANDATA" datatype="VARCHAR" size="27" />

						</DIExpression>

						<DIExpression isString="true" expr="$G_BATCH_ID" >

							<VARIABLE_REFERENCE name="$G_BATCH_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="$G_PROCESS_ID" >

							<VARIABLE_REFERENCE name="$G_PROCESS_ID" />

						</DIExpression>

						<DIExpression isString="true" expr="sysdate()" >

							<FUNCTION_CALL name="sysdate"  type="DI" />

						</DIExpression>

					</DIProjection>

					<DIFrom>

						<DITableSpec name="CONTROL_1" />

						<DITableSpec name="QRY_JOIN_METADATA" />

					</DIFrom>

					<DIWhere>

						<DIExpression isString="true" expr="((((CONTROL_1.ENTITY_NAME = QRY_JOIN_METADATA.DWID) AND&#xA;   (CONTROL_1.EXTRACT_TSNP_FLAG = 'N')) AND&#xA;   (CONTROL_1.STATUS = 'NEW')) AND&#xA;   (CONTROL_1.MD_BATCH_ID = $G_BATCH_ID))" >

							<LOGICAL_OP op="AND">

								<LOGICAL_OP op="AND">

									<LOGICAL_OP op="AND">

										<LOGICAL_OP op="EQ">

											<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="ENTITY_NAME" />

											<COLUMN_REFERENCE  qualifier1="QRY_JOIN_METADATA" column="DWID" />

										</LOGICAL_OP>

										<LOGICAL_OP op="EQ">

											<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="EXTRACT_TSNP_FLAG" />

											<CONSTANT value="N" datatype="VARCHAR" size="1" />

										</LOGICAL_OP>

									</LOGICAL_OP>

									<LOGICAL_OP op="EQ">

										<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="STATUS" />

										<CONSTANT value="NEW" datatype="VARCHAR" size="3" />

									</LOGICAL_OP>

								</LOGICAL_OP>

								<LOGICAL_OP op="EQ">

									<COLUMN_REFERENCE  qualifier1="CONTROL_1" column="MD_BATCH_ID" />

									<VARIABLE_REFERENCE name="$G_BATCH_ID" />

								</LOGICAL_OP>

							</LOGICAL_OP>

						</DIExpression>

					</DIWhere>

				</DISelect>

				<DIAttributes>

					<DIAttribute name="distinct_run_as_separate_process" value="no"/>

					<DIAttribute name="group_by_run_as_separate_process" value="no"/>

					<DIAttribute name="join_run_as_separate_process" value="no"/>

					<DIAttribute name="order_by_run_as_separate_process" value="no"/>

					<DIAttribute name="run_as_separate_process" value="no"/>

				</DIAttributes>

			</DIQuery>



			<DITransformCall typeId="111" name="Merge" >

				<DIInputView name="QRY_NON_CLEANDATA"  />

				<DIInputView name="QRY_SETVALUE"  />

				<DISchema name="Merge" >

				</DISchema>

			</DITransformCall>

		</DITransforms>

		<DIAttributes>

			<DIAttribute name="Cache_type" value="pageable_cache"/>

			<DIAttribute name="Parallelism_degree" value="0"/>

			<DIAttribute name="allows_both_input_and_output" value="yes"/>

			<DIAttribute name="run_once" value="no"/>

			<DIAttribute name="use_dataflow_links" value="no"/>

			<DIAttribute name="use_datastore_links" value="yes"/>

			<DIAttribute name="validation_xform_exists" value="no"/>

			<DIAttribute name="validation_xform_stats" value="no"/>

		</DIAttributes>

	</DIDataflow>



</DataIntegratorExport>



